Forum Discussion
Issues with Conditional Formatting in PowerBi (Format by Rules)
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,
% Index = MIN('Table'[Index])/CALCULATE(COUNT('Table'[Index]),ALL('Table'))3. Place the fields Item,Value,Index,Index% in Table chart and sort asc by Index.4. Create a new column as below,Column = SWITCH(TRUE(),[% Index]>=0 && [% Index]<=0.33,"#C0392B",[% Index]>0.33 && [% Index]<=0.66,"#3498DB",[% Index]>0.66 && [% Index]<=0.99,"#27AE60",[% Index]>0.99,"#F4D03F")5. Do conditional formatting on %Index field as below,Conditional Formatting->background color->format by field vale->choose column filed.See the expected results as below,Please mark it as a solution if this works for you.
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.
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
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)