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!

 

[barqers]

You're very Welcome.

Hey Comfy,

Sorry to resurrect this thread, but say you have a cost center in your tDimension table that is not used in the tFact table, so say you added a 3rd cost center to your example file, and added some square footage to it, and say you just wanted 'all' cost centers to show up in your pivot, now the total square footage will include that 3rd cost center even though there's no costs associated to it. Is there any way around that? I've tried adding an if statement so if 'tAmount' = 0 then sqft=0, but it doesn't seem to do anything. Any ideas?