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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Seantb
Frequent Visitor

Dynamic Formatting of Measures - rounded to nearest whole integer for thousands/millions/billions

Hi all, 

 

I have the below measure format expression used in a switch measure that users can filter through with a slicer to show actual count, actual value and then percentage for each of count and value. I need the count and value to be in thousands and millions respectively however the expression below currently only allows the data label to express to one decimal point when i would prefer an integer to the nearest thousand e.g. $612345678 is being expressed as $612.3M when i need this to be $612M and 124577 is being expressed as 124.6K when i need this to be expressed as 125K. 

Can you please advise how i need to change the below format expression in order to acheive this?

VAR __SelectedMeasure = SELECTEDVALUE(Measures_Selection[Code],1)
RETURN
SWITCH(
    TRUE(),
    __SelectedMeasure = 1, "0,.0K",
    __SelectedMeasure = 2, "0%",
    __SelectedMeasure = 3, "$#,,.0M",
    __SelectedMeasure = 4, "0%"
)
3 REPLIES 3
Seantb
Frequent Visitor

@Adamboer - I've been able to get the tooltip to show the values in Millions (M) or Thousands (K), but the clustered column chart labels themselves still are not showing it. 

I've changed to the below format expression and when i hover over the graph and view the tooltip I can see the value correctly, refer "Graphed Metric" referring to the measure that I have chosen in slicer at that time. The graph itself though shows the data label as 13206K for som reason. When i try changing the 'Value decimal places' in the data labels from 0 to 1 the graph appears as 13.2K  as expected, but changing it back to '0' or 'auto' has it revert to 13206K. I have the Display units as 'None', changing that to anything other than 'None' removes the correct label entirely.  Are you aware what could be causing this?

Seantb_0-1681863978788.png

 

VAR __SelectedMeasure = SELECTEDVALUE(Measures_Selection[Code],1)
RETURN
SWITCH(
    TRUE(),
    __SelectedMeasure = 1, "#,.K",
    __SelectedMeasure = 2, "0%",
    __SelectedMeasure = 3, "$#,,.M",
    __SelectedMeasure = 4, "0%"
)
Seantb
Frequent Visitor

Thank you for responding, however I've tried your suggestion and it has not worked as required. 

 

My format expression is now what you have posted but the data labels are now formatted as straight numbers with M/K at the end. e.g. $612345678 is being expressed as $612345678M and 124577 is being expressed as 124577K.

Adamboer
Responsive Resident
Responsive Resident

To display the count and value in thousands and millions respectively, you need to modify the format expression in the SWITCH function. To achieve this, you can replace the current format expression for value with "$#,,""M"";-#,,""M"";0" and the format expression for count with "##,,"K;##,,"K;-##,,"K;0". This will format the value in millions and count in thousands with integers to the nearest thousand.

So, the modified measure format expression would be:

VAR __SelectedMeasure = SELECTEDVALUE(Measures_Selection[Code],1) RETURN SWITCH( TRUE(), __SelectedMeasure = 1, "##,,""K"";##,,""K"";-##,,""K"";0", __SelectedMeasure = 2, "0%", __SelectedMeasure = 3, "$#,,""M"";-#,,""M"";0", __SelectedMeasure = 4, "0%" )

By using this modified format expression, you should be able to display count in thousands and value in millions with integers to the nearest thousand, instead of decimals.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.