Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Anonymous
Not applicable

@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
kentyler
Solution Sage
Solution Sage

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!




Join the conversation at We Talk BI find out more about me at Slow BI


Anonymous
Not applicable

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





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!




Join the conversation at We Talk BI find out more about me at Slow BI


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!




Join the conversation at We Talk BI find out more about me at Slow BI


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!




Join the conversation at We Talk BI find out more about me at Slow BI


Anonymous
Not applicable

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

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

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!




Join the conversation at We Talk BI find out more about me at Slow BI


Anonymous
Not applicable

@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





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!




Join the conversation at We Talk BI find out more about me at Slow BI


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.