Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
jyouens
Frequent Visitor

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 I change the data type to Decimal and have 2 decimal places, or whole numbers the conditional formatting isnt working as expected.

 

jyouens_0-1709290519561.png

The issue is with row 3 in the example and my confitional formatting is as per the below

 

The new column is Column B - Column A

jyouens_1-1709290585676.png

The column I'm basing the conditional formatting off is from a DAX measure and the other cells that it is calculated off is from a DAX table

 

Any ideas?

 

 

 

1 ACCEPTED SOLUTION
v-jingzhan-msft
Community Support
Community Support

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!

View solution in original post

4 REPLIES 4
jyouens
Frequent Visitor

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_CODEMONTH_OF_SERVICEACTUALIZED_COST
TEST2024/01148212.4
TEST2024/012964.25
TEST2024/011026.38
TEST2024/0172067.21
TEST2024/011441.34
TEST2024/016824.4
TEST2024/01137356.09
TEST2024/012747.12
TEST2024/011445.78
TEST12024/0125407.68
TEST12024/01

0

 

Table - Tracker_Recons

 

Client CodeDateclient_cost
TEST01/01/2024151176.65
TEST01/01/202473508.55
TEST01/01/2024140103.21
TEST101/01/202425407.68
TEST01/01/20240
TEST01/01/20241026.38
TEST01/01/20246824.4
TEST01/01/20241445.78
TEST101/01/20241661.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
 
jyouens_0-1709554605997.png

 

Hope this helps?

v-jingzhan-msft
Community Support
Community Support

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!

this worked, thank you! @v-jingzhan-msft 

lbendlin
Super User
Super User

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.