Forum Discussion
Solved
Conditional Formatting - Icons - Scientific Numbers
I am wanting to use the icons conditional formatting option on a column which is based off another column I have created The issue is that the new column is returning a scientific number. Even if...
Hi jyouens
You may try rounding the numbers visibly in the measure for conditional formatting. For example,
Rule Measure = ROUND ( SUM ( 'Table'[Column B] ), 0 ) - ROUND ( SUM ( 'Table'[Column A] ), 0 )You can also choose to use ROUNDDOWN or ROUNDUP per your need.
Hope this would be helpful.
Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!
Hi lbendlin - thanks for the pointer
Please see the data below, taken from Excel files that I've loaded into power bi. I cannot share the pbix file due to sensitive info
Table - Test_Placement_Actualisation
| ADVERTISER_CODE | MONTH_OF_SERVICE | ACTUALIZED_COST |
| TEST | 2024/01 | 148212.4 |
| TEST | 2024/01 | 2964.25 |
| TEST | 2024/01 | 1026.38 |
| TEST | 2024/01 | 72067.21 |
| TEST | 2024/01 | 1441.34 |
| TEST | 2024/01 | 6824.4 |
| TEST | 2024/01 | 137356.09 |
| TEST | 2024/01 | 2747.12 |
| TEST | 2024/01 | 1445.78 |
| TEST1 | 2024/01 | 25407.68 |
| TEST1 | 2024/01 | 0 |
Table - Tracker_Recons
| Client Code | Date | client_cost |
| TEST | 01/01/2024 | 151176.65 |
| TEST | 01/01/2024 | 73508.55 |
| TEST | 01/01/2024 | 140103.21 |
| TEST1 | 01/01/2024 | 25407.68 |
| TEST | 01/01/2024 | 0 |
| TEST | 01/01/2024 | 1026.38 |
| TEST | 01/01/2024 | 6824.4 |
| TEST | 01/01/2024 | 1445.78 |
| TEST1 | 01/01/2024 | 1661.73 |
EXM_Tracker_Recons = ADDCOLUMNS
(SUMMARIZE(Test_Placement_Actualisation,Test_Placement_Actualisation[ADVERTISER_CODE],Test_Placement_Actualisation[MONTH_OF_SERVICE]),
"EXM Reconciled Amount",CALCULATE(SUM(Test_Placement_Actualisation[ACTUALIZED_COST])),
"Tracker Amount",CALCULATE(SUM(Tracker_Recons[CLIENT_COST]),
FILTER(ALL('Tracker_Recons'),Tracker_Recons[CLIENT_CODE] = EARLIER('Test_Placement_Actualisation'[ADVERTISER_CODE]) && Tracker_Recons[DATE] = EARLIER(Test_Placement_Actualisation[MONTH_OF_SERVICE]))))
And then I've created a measure to work out the difference
SUM('Prisma_Tracker_Recons'[Tracker Amount]) - SUM('Prisma_Tracker_Recons'[Prisma Reconciled Amount])
on this measure is where I am trying to do the conditional formatting so that if the above calculation = 0 for all rows then the green tick is applied with the conditional formatting and then the red cross if it doesn't match
The data from the 2 tables does match to 2 decimal places, but the measure is showing a scientific number as the value is so small. I have edited the formatting for all tables and the measure to be whole numbers but the issue persists
So based on the below example, I would expect the 401,154 to have a green tick in column B
Hope this helps?