Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi Community,
I'm faicng issues with conditional formatting while using format by rules option. My criteria fits as below:
1. 0 - 33% Red color
2. 34 - 66% Blue color
3. 67 -99% Green Color
4. 100% Yellow..
along with 0-33%, 34th % is also getting formatted in Red color; 67th % is also getting formatted in Blue color!
Solved! Go to Solution.
Hi @Anonymous ,
Please follow the below steps to achieve your requirement,
1. Create Index column starting with 1 (Can Create in Query Editor)
2. Create a measure as below,
Hi @Anonymous ,
It is by design. The percentage is calculated based on Range.
Percentage(in conditional formatting)
For your requirement, you could try to create a DAX calculation. Then set the color formatted by field value. For more details, you could reference my sample.
Measure 2 = SWITCH(TRUE(),
MAX('Table'[Column]) >=0 && MAX('Table'[Column])<= 0.33,"red",
MAX('Table'[Column]) >0.33 && MAX('Table'[Column])<= 0.66,"blue",
MAX('Table'[Column]) >0.66 && MAX('Table'[Column])<= 0.99,"green",
MAX('Table'[Column]) >0.99,"yellow","black"
)
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-xuding-msft ,
Thanks for your help. My concern here is my values might be ranging between 1 - 1Million. I need my data to be segragated into equal halves. like the top 33% of values to marked in red and next 33% in green and next 33% in blue and last 1% in yellow.
In the above question posted, please consider values column for referencenhvaing dynamic range of values.
Thanks.
Hi @Anonymous ,
Please follow the below steps to achieve your requirement,
1. Create Index column starting with 1 (Can Create in Query Editor)
2. Create a measure as below,
Hi @Anonymous ,
Thanks. This works amazing for given scenario :).
But there is still a concern related to it, what if my values are unique but have different Index number and hence there is a difference in Index%. I doubt there might be slightest chances for error.
Hi @Anonymous
Thank you.
Index here is not dependant of values. It only works on number of records that we have in a table.
For example, if you have 500 records out of it having 20 unique values, the index still work from 1 to 500.
As I understood from your req is you want to conditionally format the records based on % of records as 0-33%,33-66%,66-99%,100%.
So, I think we don't need to worry about the unique values.
My bad. Apologies!
It's actually dependent on the values! 33% of each of the unique values to be formatted accordingly ( if distinct, the 'values' is to be treated as one)
Hey @Anonymous ,
As i suspected, this creates a problem if more values are added!
Hi @Anonymous ,
Add the Index Column in Query Editor. I am not sure how you have included Index column.
See the below as I have unique values for 3 items biut still they have different index.
Hi @Anonymous ,
i did add index colmn using power query editor.
I need all the values with 456 to be conditionally formatted under single color code.
Thanks!
User | Count |
---|---|
80 | |
74 | |
62 | |
61 | |
46 |
User | Count |
---|---|
108 | |
97 | |
88 | |
81 | |
61 |