Forum Discussion

dpc_development's avatar
dpc_development
Helper III
4 years ago

Visual Axes gets double formatted when using calculation groups

My client had a requirement where the numbers in the tooltips should be beautified - so a number like 3,493,380 should show as 3.49M.

 

To solve this I used the following custom string expression in a calculation group.

var val = SELECTEDMEASURE()
var digits = IF(ISNUMBER(val), LEN(ROUND(val, 0)), -1)
var prefix = IF(digits > 0 && val < 0, "-", "")

RETURN

IF(
    digits < 5, SELECTEDMEASUREFORMATSTRING(),
    
    SWITCH(
        digits,
        5, prefix & "0,.00K",
        6, prefix & "0,.00K",
        7, prefix & "0,,.00M",
        8, prefix & "0,,.00M",
        9, prefix & "0,,.00M",
        10, prefix & "0,,,.00B",
        11, prefix & "0,,,.00B",
        12, prefix & "0,,,.00B",
        13, prefix & "0,,,,.00T",
        14, prefix & "0,,,,.00T",
        15, prefix & "0,,,,.00T",
        SELECTEDMEASUREFORMATSTRING()
    )
    
)

 

The above works, but when I use it in a Visual the X / Y Axis Numbers gets messed up, as Power BI does double formatting. Please see the before and after images below.

 

Without Calculation Group adjustment:

 

With Calculation Group adjustment:

 

As can be seen, in charts like the above where Power BI already has intelligence to show values in M, adding my custom format basically appends on top of what Power BI has already done.

 

Perhaps, I need to not trigger the formatting for values plotted in the Axes.

 

Can someone suggest what I can do in this situation.

 

OwenAuger 

8 Replies