Conditional formatting gives incorrect results using % but works with number equivalent

[DataSkills]

I have a matrix and I am putting icons on the values, based on a measure which works out the % of GT. However, when I use the percent option, I get the wrong results. I have to change to the equivalent number to get the right results. This makes no sense to me. Can anyone explain? 

RULES:

• Over 10% as red icon
• Between 5% and 10% as amber icon
• Below 5% as green icon

ATTEMPT 1 (This one gives incorrect results)

Notice all the invalid results. Cameras and camcorders for Asia is 6.77% which should be an amber triangle. But it's giving me a red circle. 

However, changing the conditional formatting from percent to number works. I converted the amounts (eg 5% becomes 0.05).  Surely, these are the same? 0.1 = 10%!!

ATTEMPT 2

I hope someone can tell me what I am not understanding here. 

[Selva-Salimi · Accepted solution]

Hi @DataSkills 

as you select percent in conditional formatting. this will calculate that percentage based on minumum and maximum and then choose the color based on its calculation. so if you yourself calculate the percentage before, it will be better to use numbers.

If this post helps, then I would appreciate a thumbs up  and mark it as the solution to help the other members find it more quickly.

[Selva-Salimi · Accepted solution]

Hi @DataSkills 

as you select percent in conditional formatting. this will calculate that percentage based on minumum and maximum and then choose the color based on its calculation. so if you yourself calculate the percentage before, it will be better to use numbers.

If this post helps, then I would appreciate a thumbs up  and mark it as the solution to help the other members find it more quickly.

[DataSkills]

Hi @Selva-Salimi thanks for your quick response. I was attempting to figure out what percentage would be calculated based on what you said. I've simplified the table to only show the Total COS column. 

I am trying to figure out how it calculates the results:

Take the figure for Cell phones for Asia - £572,042. It's got an amber icon, indicating that the value must lie between 5% & 10%. 

Compared against the max value overall (£5,377,675) it's 10.6% which would be a red icon. 

Compared against the overall grand total (£23,804,323) it's 2.4% which would be a green icon.

Compared against the column total for Asia (£5,267,651) it's 10.86% which would be a red icon. 

I am still at a loss to figure out how it is getting the result it is displaying. 

[Anonymous · Accepted solution]

Hi,Selva-Salimi ,thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below.

Hello,@DataSkills .I am glad to help you.
I tested the suggestion made by Selva-Salimi and found the relevant description in the official documentation for setting the conditional format, which makes it more intuitive to see the difference between percent and number Here is my test:

M_number = 
VAR _ExpectIncome=MAX('Sales'[Expected income])
VAR _ActualIncome=MAX('Sales'[Actual income])
VAR Diff = ABS(_ActualIncome - _ExpectIncome) / _ExpectIncome
RETURN
   Diff

Links to related documentation.
URL:
Apply conditional table formatting in Power BI - Power BI | Microsoft Learn
You should use number as a conditional format judgment as you mentioned (using the decimal type)

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

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