Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I was wondering how I could use conditional formatting in a matrix using percentiles for a measure. So, I have a measure broken up by a few dozen people in a matrix. I want the top 25% of these people (in terms of this measure) to be one color, the next 25% to be a different color, and so on.
I've included a picture of what I'm talking about. I just have a regular color scale here, but I want my measure, PPH, to be colored by each quartile as I explained above. Any ideas?
Thanks
Solved! Go to Solution.
@kentyler Ahhh I see, thanks for the suggestion! I just realized however that I need to be able to change the values of PPH with a date filter, so I have a slicer on my main page which changes the dates I pull my PPH data from, and thus changes PPH. The date range and thus PPH need to be able to be changed at will. You did however give me some good ideas as to how to move forward, so thank you!
There are 2 functions for calculating the percentile of a value, PERCENTILE.EXC and PERCENTILE.INC. According to a post I found "
both of these formula exclude one number from your series.
I created a calculated column using both measures and got a result of 1,2,3,4 for each value. Unfortunately
PPH | Quartile_INC | Quartile_EXC |
2.84 | 1 | 1 |
2.93 | 1 | 1 |
3.19 | 1 | 1 |
3.24 | 1 | 1 |
3.34 | 2 | 2 |
3.48 | 2 | 2 |
3.5 | 2 | 2 |
3.57 | 3 | 3 |
3.8 | 3 | 3 |
3.82 | 3 | 3 |
4.04 | 4 | 3 |
4.53 | 4 | 4 |
4.71 | 4 | 4 |
6.15 | 4 | 4 |
you can see that in one case 4.04 comes out 3 and in the other 4. You will have to decide whether to use INC or EXC
Here are the 2 calculated columns
Help when you know. Ask when you don't!
@kentyler Thank you very much for your reply. However, PPH in my case is a measure, not a column in a sheet. What can I do in this case?
If you can post the text of the measure, I think I can show you how to incorporate it.
Help when you know. Ask when you don't!
percentile works against a column. so perhaps you will have to use the measure to create a calculated column
Help when you know. Ask when you don't!
Here is some code that solves a similar problem using a measure that returns the quarter number:
Quartile = var FirstQ = CALCULATE(PERCENTILE.INC(Table1[Column1], .25), ALL(Table1[Column1])) var SecondQ = CALCULATE(PERCENTILE.INC(Table1[Column1], .50), ALL(Table1[Column1])) var ThirdQ = CALCULATE(PERCENTILE.INC(Table1[Column1], .75), ALL(Table1[Column1])) var ThisVal = Min(Table1[Column1]) return IF(HASONEVALUE(Table1[Column1]), IF(ThisVal <= FirstQ, 1, IF(ThisVal > FirstQ && ThisVal <= SecondQ, 2, IF(ThisVal > SecondQ && ThisVal <= ThirdQ, 3, 4) ) ) )
But it still would not address the issue that percentile.inc takes a column reference
Help when you know. Ask when you don't!
@kentyler Thank you again for your replies. The text of the measure is
Since percentile depends on a column reference, I think you will have to write your measure as a calculated column, and then refer to that column in order to get your ranking values. You can use the DIVIDE() function to protect against divide by zero errors.
Help when you know. Ask when you don't!
@kentyler Ahhh I see, thanks for the suggestion! I just realized however that I need to be able to change the values of PPH with a date filter, so I have a slicer on my main page which changes the dates I pull my PPH data from, and thus changes PPH. The date range and thus PPH need to be able to be changed at will. You did however give me some good ideas as to how to move forward, so thank you!
If you have problems incorporating the Measure I'd be glad to do a screen share and walk you thru it. Send me an email and I'll send you a link. ken@8thfold.com
Help when you know. Ask when you don't!
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |