Issues with Conditional Formatting in PowerBi (Format by Rules)

[Anonymous]

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!

[v-xuding-msft]

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.

[Anonymous]

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.

[Anonymous · Accepted 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,

% 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.

[Anonymous]

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.

[Anonymous]

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.

[Anonymous]

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)

[Anonymous]

Hey @Anonymous ,

As i suspected, this creates a problem if more values are added!

[Anonymous]

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.

[Anonymous]

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!