Forum Discussion
Max of a Measure Used In Another Measure
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.
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
3 Replies
- mahoneypat
Microsoft Employee
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
- Ashish_Mathur
Super User
Hi,
Does this measure work?
=[Percent]-maxx(all(data[workers]),[percent])
