Forum Discussion
Conditional formatting using percentiles in a matrix
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
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!
9 Replies
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.
- inc includes first number of the series and excludes last number.
- Exc excludes first number of series and includes last number."
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
Quartile_EXC = VAR QUARTILE_1 = PERCENTILE.EXC(Sheet1[PPH],.25)VAR QUARTILE_2 = PERCENTILE.EXC(Sheet1[PPH],.5)VAR QUARTILE_3 = PERCENTILE.EXC(Sheet1[PPH],.75)VAR CURpph = Sheet1[PPH]VAR RETURNVAL = IF(CURPPH <= QUARTILE_1,1,IF(CURPPH <= QUARTILE_2,2,IF(CURPPH<=QUARTILE_3,3,IF(CURPPH>QUARTILE_3,4))))RETURN RETURNVALQuartile_INC = VAR QUARTILE_1 = PERCENTILE.INC(Sheet1[PPH],.25)VAR QUARTILE_2 = PERCENTILE.INC(Sheet1[PPH],.5)VAR QUARTILE_3 = PERCENTILE.INC(Sheet1[PPH],.75)VAR CURpph = Sheet1[PPH]VAR RETURNVAL = IF(CURPPH <= QUARTILE_1,1,IF(CURPPH <= QUARTILE_2,2,IF(CURPPH<=QUARTILE_3,3,IF(CURPPH>QUARTILE_3,4))))RETURN RETURNVALthere are the same except for the function used
You can use the conditional formatting of cells in a column to change the color based on the 1,2,3,4 value
