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!
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!
