Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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!

kentyler's avatar
kentyler
Solution Sage
6 years ago

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

kentyler's avatar
kentyler
Solution Sage
6 years ago

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
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

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

    PPH = IFERROR(
                     SUM('Data'[Solves])/SUM('Data'[Duration]),
                     BLANK()
    )
    • kentyler's avatar
      kentyler
      Solution Sage
      6 years ago

      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.

      • kentyler's avatar
        kentyler
        Solution Sage
        6 years ago

        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. [email protected]