cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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?

 

example power bi.PNG

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Anonymous
Not applicable

Re: Conditional formatting using percentiles in a matrix

@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!

View solution in original post

9 REPLIES 9
Super User II
Super User II

Re: Conditional formatting using percentiles in a matrix

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.

  1. inc includes first number of the series and excludes last number.
  2. 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 

 
 
PPHQuartile_INCQuartile_EXC
2.8411
2.9311
3.1911
3.2411
3.3422
3.4822
3.522
3.5733
3.833
3.8233
4.0443
4.5344
4.7144
6.1544
   

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 RETURNVAL
 
Quartile_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 RETURNVAL
 
there 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





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Anonymous
Not applicable

Re: Conditional formatting using percentiles in a matrix

@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?

Super User II
Super User II

Re: Conditional formatting using percentiles in a matrix

If you can post the text of the measure, I think I can show you how to incorporate it.





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Super User II
Super User II

Re: Conditional formatting using percentiles in a matrix

percentile works against a column. so perhaps you will have to use the measure to create a calculated column





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Super User II
Super User II

Re: Conditional formatting using percentiles in a matrix

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




Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Anonymous
Not applicable

Re: Conditional formatting using percentiles in a matrix

@kentyler Thank you again for your replies. The text of the measure is 

PPH = IFERROR(
                 SUM('Data'[Solves])/SUM('Data'[Duration]),
                 BLANK()
)
Super User II
Super User II

Re: Conditional formatting using percentiles in a matrix

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.





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Super User II
Super User II

Re: Conditional formatting using percentiles in a matrix

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





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Anonymous
Not applicable

Re: Conditional formatting using percentiles in a matrix

@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!

View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors