Forum Discussion
Conditional formatting gives incorrect results using % but works with number equivalent
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.
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 DiffLinks 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.
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.
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.
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 DiffLinks 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.