Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi everyone,
I would like to change my conditional formatting the following way:
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
Solved! Go to 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]
)
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]
)
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
Proud to be a Super User!
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |