Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I have a measure that calculates a perentage. Lets say it looks like the table below:
Client | Percent |
a | 45% |
b | 20% |
c | 5% |
d | 1% |
What I want to do is add another column that finds the max percentage overall and then subtracts it by each individual row. So it would look like the following:
Client | Percent | Percent Loss |
a | 45% | 0 |
b | 20% | -25% |
c | 5% | -40% |
d | 1% | -44% |
Ideally it would also work when drilling into the data. So if inside Client a, I have 3 workers who have their own percentage, I would like the calcuation to then work on their percentage EX:
Client | Percent | Percent Loss |
a | 45% | |
Tom | 55% | 0% |
Jill | 35% | 20% |
Eve | 20% | 35% |
So Tom, Jill and Eve's data all together make up the 45% of client a, but as you can see, each has their own calculated percentage which can be higher than client a's.
Solved! Go to Solution.
You can use a measure pattern like this to get your result.
Percent Loss = var vThisClient = [Percent]
var vMaxAll = MAXX(ALLSELECTED(Table[Client]), [Percent])
return vThisClient - vMaxAll
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hi,
Does this measure work?
=[Percent]-maxx(all(data[workers]),[percent])
You can use a measure pattern like this to get your result.
Percent Loss = var vThisClient = [Percent]
var vMaxAll = MAXX(ALLSELECTED(Table[Client]), [Percent])
return vThisClient - vMaxAll
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
This worked perfect. I did add a If(IsFiltered... to get it to work on the drill down, but I just used this formula twice for each case. Thank you.
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
60 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |