Max of a Measure Used In Another Measure

[Anonymous]

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.

[Ashish_Mathur]

Hi,

Does this measure work?

=[Percent]-maxx(all(data[workers]),[percent])

[mahoneypat · Accepted 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

[Anonymous]

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.