Forum Discussion
Issues with Conditional Formatting in PowerBi (Format by Rules)
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 Colo...
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,
% 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.