Filter out unique values based on multiple columns using DAX/PowerPivot?

[barqers]

I know I'm in the Power BI forum, sorry about that, but I wanted to know if it's possible to select unique values only using multiple columns?

Ex. I have a table that has the following columns:
CostCenter# Account# Month# SpendAmt


What happens though is for instance say we have a cost center that does 3 repairs in month 1 there will be 3 rows, that have identical cost centers, account numbers, and month numbers, just the spend amount will be different (usually). However, what I want to do is pull in square footage of the cost center from another table. Problem is if I bring in this data it'll show the square footage 3 times so let's say it's 85,000sqft, when I go to create a powerpivot instead of showing for instance that we have cost center 1, for account 101010, in month 1, we spent $2,000 (in 3 transactions) the square footage will show as 85,000*3 = 255,000. However, if I change the field to an average, I get the correct 85,000, but when I go to create a calculated field of spend amt / square footage, it automatically does $2,000/255,000 instead of the $2,000/85,000. To eliminate this what I want to do in PowerPivot is create a calculated column that brings in the square footage but only once if the cost center, account number, and month are all the same. This will show 85,000 in my pivot using a sum formula, then my calculated field will be $2,000/85,000. Is there a better way?

I've tried Distinct (seems to only work I guess in PowerBI?), rankx (can't seem to get it to show the ranking properly), and summarize but really wasn't sure what to do with all 3. I figured if I could rank based on the 3 criteria, that every rank 1 I would show the square footage. Any ideas? Thank you for the help!

 

[Comfy]

This sounds like you have pulled the SqFt into your Fact table?

You need two tables:

tFact
Excel 2010

	A	B	C	D
1	CostCenter	Account	Month	SpendAmt
2	1234	456789	01/09/2016	1000
3	1234	456789	01/09/2016	550
4	1234	456789	01/09/2016	450
5

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

tDimension
Excel 2010

	G	H
1	CostCenter	Area
2	1234	85000
3

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Then create 3 measures.

tDimension Measure
TotalArea:=Sum([Area])

tFact Measures
tAmount:=SUM([SpendAmt])
AmountSqft:=tFact[tAmount]/tDimension[TotalArea]

Use CostCentre and AmountSqFt in your pivot.

 

[barqers]

This sounds like you have pulled the SqFt into your Fact table?

You need two tables:

tFact
Excel 2010

	A	B	C	D
1	CostCenter	Account	Month	SpendAmt
2	1234	456789	01/09/2016	1000
3	1234	456789	01/09/2016	550
4	1234	456789	01/09/2016	450
5

<tbody>
</tbody>

tDimension
Excel 2010

	G	H
1	CostCenter	Area
2	1234	85000
3

<tbody>
</tbody>

Then create 3 measures.

tDimension Measure
TotalArea:=Sum([Area])

tFact Measures
tAmount:=SUM([SpendAmt])
AmountSqft:=tFact[tAmount]/tDimension[TotalArea]

Use CostCentre and AmountSqFt in your pivot.

Hi Comfy,

I see where you're going with this, but what if for instance there's several accounts aka Rent (1001), Janitorial (1002), Repairs (1003), etc. and also several cost centers Toronto (10) Ottawa (11) and each cost center/account combo will have months 1-12. So we're looking at like 3*2*12=72 rows of data. If I use your method, I seem to be getting the cost/square foot but in total for all 72 rows, but what I want to accomplish is the cost per square footage for each distinct grouping, in this case 2 cost centers * 3 account codes = 6 distinct groupings, aka Cost center 10, account 1001 = $x/sqft. Any thoughts on this?

 

[Comfy]

This should still work (I think, I'm super new to DAX)

It is vital that you use the [CostCenter] field from your Dimension table rather than Fact table.

 

[barqers]

This should still work (I think, I'm super new to DAX)

It is vital that you use the [CostCenter] field from your Dimension table rather than Fact table.

So when I add tAmount I just get for instance $3,061,128 in every single row, for TotalArea I get let's say 19,677 so in the AmountSqft I just get 15.56 in every row. When I go to a pivot table I'm only using cost center as a slicer, so I end up just seeing 15's everywhere.

I was looking into SUMMARIZE, but it appears you can't create a summarize table in powerpivot?

 

[Comfy]

I don't know I'm afraid.

This is the sample file I was using: Mega File Upload - 977382.xlsx

Hopefully it'll help. As I appear to be getting the correct answers.

 

[barqers]

I don't know I'm afraid.

This is the sample file I was using: Mega File Upload - 977382.xlsx

Hopefully it'll help. As I appear to be getting the correct answers.

Hey Comfy, thank you so much for the file. I would've been stuck forever without it. I didn't realize that a measure goes in the area below the table... I was adding a new column. Oops! Now I know what that area down below is for? Amateur mistake but you solved the solution so eloquently and it took me 3 days to figure out nothing haha. Thanks again!

 

[Comfy]

Hey Comfy, thank you so much for the file. I would've been stuck forever without it. I didn't realize that a measure goes in the area below the table... I was adding a new column. Oops! Now I know what that area down below is for? Amateur mistake but you solved the solution so eloquently and it took me 3 days to figure out nothing haha. Thanks again!

You're very Welcome.

 

[barqers]

You're very Welcome.

Hey Comfy,

One quick question! Sorry, but say I pull the BIO out of a row label, and instead add the Account # I then get an error saying to create a relationship. I think that's because for instance the tFact table doesn't have a relationship to the tDimension table on the 'Account'. Is there any way around that?

 

[barqers]

Hey Comfy,

One quick question! Sorry, but say I pull the BIO out of a row label, and instead add the Account # I then get an error saying to create a relationship. I think that's because for instance the tFact table doesn't have a relationship to the tDimension table on the 'Account'. Is there any way around that?

Scratch that sorry, I was using a third table with a list of accounts and their descriptions, but that third table had no relationship to the tDimension table for instance since tDimension doesn't deal with accounts. Instead I just pull in the description from tAccountDescription into tFact as another column and works

Thanks again!