Forum Discussion

Titatovenaar2's avatar
Titatovenaar2
Icon for Advocate II rankAdvocate II
4 years ago
Solved

DAX Conditional Formatting in matrix based on Percentiles per Category

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

 

Need Help
  • Titatovenaar2's avatar
    Titatovenaar2
    4 years ago

    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]
)

3 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      Titatovenaar2
      Icon for Advocate II rankAdvocate II
      4 years ago

      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.

  • Titatovenaar2's avatar
    Titatovenaar2
    Icon for Advocate II rankAdvocate II
    4 years ago

    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]
)