Missed icons for table conditional formatting

Hi everybody,

I think my issue is quite simple but I'm really unable to find a solution.

I create a simple table with these data and tried conditional formatting with icons.

So, I don't understand with almost all my icons are green. Values between 127 and 254 would be yellow, but they are not!

What am I missing?

Thank you

Need Help

v-joesh-msft

6 years ago

Hi Anonymous ,

You can use the IF statement to nest, and change the measure: "% CT 2 with area" in the previous demo to the following formula：

% CT 2 with area =
IF (
    ISINSCOPE ( 'Table (2)'[ Area] ),
    DIVIDE (
        SUM ( 'Table (2)'[Qty] ),
        CALCULATE ( SUM ( 'Table (2)'[Qty] ), ALLSELECTED ( 'Table (2)'[ Area] ) )
    ),
    DIVIDE (
        SUM ( 'Table (2)'[Qty] ),
        CALCULATE (
            SUM ( 'Table (2)'[Qty] ),
            ALLSELECTED ( 'Table (2)'[MercatoAgente] )
        )
    )
)

Best Regards,

Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

11 Replies

PattemManohar
Community Champion
6 years ago
Are you sure that you are conditional formatting is on Percent instead of actual values ? If not, then change the "Percent" to "Number" in the drop-down under Rules.
jdbuchanan71
Super User
6 years ago
Anonymous

The way the "Percent" conditional formatting works is different than you think, it doesn't have anything to do with percent of total. It take the lowest and hightest displayed amounts and uses those to determine the formatting bands based on the Percent you entered so your table is looking at these numbers to apply the formatting.

What you want to do is add your value again and display change the "Show value as" > Percent of grand total.

Then apply your conditional formatting to that column based on numbers, not percent.

>= 0, NUMBER < .1 NUMBER

>= .1 NUMBER < .2 NUMBER

>= .2 NUMBER < 1 NUMBER
- Anonymous
  Not applicable
  6 years ago
  jdbuchanan71
  
  It'so weird that percentage conditional formatting works this way. Why would it consider the percentage as the range between maximum and minimum?
  
  Anyway, it explains my icons correctly, but if I try to follow your hint, adding the filed again showing as "Percent of grand total", I don't know how to have conditional formatting based on that percentage:
  
  As you can see, I see no icon while formatting 3rd column of my table. It's because I don't know how to suggest to format based on % of ShipmentGross and not just based on Sum of ShipmentGross.
  
  Moreover, if percentual conditional formatting works like you say, why in the following image is 11.438 with red icon? It's the maximum value of its row, so it should be green, and even if it takes max and min from column, than 205.225 should be red.
  
  Thanks you very much for your help
  - v-joesh-msft
    Solution Sage
    6 years ago
    Hi Anonymous ,
    The conditional formatting takes the difference between the minimum and maximum values as the cardinality of Percent, not the total.
    For the first question, if you want to present the conditional formatting as a percentage of the total, you can try to build a measure, such as the following:
    (1)Create a measure to calculate the total percentage：
    percentage =
    DIVIDE (
    SUM ( test[shipmentgross] ),
    CALCULATE ( SUM ( test[shipmentgross] ), ALLSELECTED( test ) )
    )
    (2) Change the Based on field to “percentage”, change “Percent” to “Number”, and modify the corresponding value. The result is as follows：
    For the second question, this involves row context in percent calculation of power bi, please try to use these three measure in conditional formatting:
    (1)% CT = DIVIDE(SUM('Table'[Qty]),CALCULATE(SUM('Table'[Qty]),ALLSELECTED('Table'[MercatoAgente])))
    (2)% GT = DIVIDE(SUM('Table'[Qty]),CALCULATE(SUM('Table'[Qty]),ALLSELECTED('Table'[Type])))
    (3)% RT = DIVIDE(SUM('Table'[Qty]),CALCULATE(SUM('Table'[Qty]),ALLSELECTED('Table')))
    
    Here is demo , please try it：
    https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/ERTo4bFI8XJJh9v5dmTqo7YB91wWAR_2mHPOvBBMJ4Nqiw?e=GIC8dy
    
    Best Regards,
    Community Support Team _ Joey
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.