DAX Conditional Formatting in matrix based on Percentiles per Category

[Titatovenaar2]

Hi everyone,

I would like to change my conditional formatting the following way:

• Mark the field <Sales_HP> and <Sales_HP%> red, for the Lowest 10% of the <Sales_HP%> field, per <Persona>.
  The <Sales_HP%> fields consists of <Sales_HP> divided by <Sales>.
• Mark the field <Sales_HP> and <Sales_HP%> orange, for the between 20-30% of the lowest records of the <Sales_HP%> field, per <Persona>.

As you can see for Persona A, the Lowest 10% of the records in this case is only CustomerID 1, and for Persona B this is CustomerID 20.

<Sales>, <Sales_HP> and <Sales_HP%> are all measures.

Is there a way of creating a measure that can be used in the following field:

using here the <Sales_HP%> measure doesn't yield the right results.

Any suggestion on how to do this is appreciated a lot!

Kind regards,

Igor

[Titatovenaar2 · Accepted solution]

Alright, that gave me an idea though which in the end solved the problem!

I ranked <Sales_HP%> per Persona first with measure <Rank>

Then I took the maximum <Rank> per persona with measure <Max Rank>

Followed by dividing the <Rank> by <Max Rank> with measure <Rank / Max Rank>

Using those numbers give me the correct Percentiles I am looking for:

Rank = 
RANKX ( FILTER( ALL ( Sales ), Sales[Persona] = MAX( Sales[Persona] ) ), Sales[Sales HP%], , ASC )

Max Rank = 
MAXX ( ALLSELECTED ( Sales[CustomerID] ), [Rank] )

Rank / Max Rank = 
DIVIDE(
    [Rank]
    ,[Max Rank]
)

[PhilipTreacy]

Hi @Titatovenaar2 

Download example PBIX file with the following data and visuals.

I'm not following the logic you used to work out the formatting.  If you want the bottom 10% red then shouldn't that be CustomerID's 1, 10, 17 and 20?  They all have Sales HP% values of 10% of less.

Regarding the orange values, you state these are for values betwen 20 and 30%.  What about values between 10 and 20%?  I've assumed you meant orange for values between 10 and 30%.

This is the Conditional Formatting rule I used for both Sales_HP and Sales_HP% (one rule for each)

Regards

Phil

[Titatovenaar2]

Hi Phil,

Thanks for your reply!

Unfortunately it is not yet what I am looking for. I am not looking for literally the numbers of 0-10% and 10-30%, I am looking for the Lowest 10% percentile Per Persona. Meaning that Only CustomerID 1 with 5% <Sales_HP%> can be coloured RED and CustomerID 10 with 10% should not color red. With 10 records for Persona A, only 1 can be allowed in the 10% lowest percentile group.

So I have the idea that it has to include some sort of ranking measure that divides the percentiles, but not sure how to do that.