Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 Color

4. 100% Yellow..

 

along with 0-33%, 34th % is also getting formatted in Red color; 67th % is also getting formatted in Blue color!

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    6 years ago

    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.
     
     
     

10 Replies

  • v-xuding-msft's avatar
    v-xuding-msft
    Community Support
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        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.
         
         
         
  • vnl3's avatar
    vnl3
    Frequent Visitor
    1 year ago

    This option does not work on the column chart visualizations in the desktop version. Any updates on the fix?