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
dpc_development
Helper III
Helper III

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:

dpc_development_0-1642826727115.png

 

With Calculation Group adjustment:

dpc_development_1-1642826760747.png

 

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 8
parry2k
Super User
Super User

@dpc_development since @OwenAuger already tested it, but I will try to test at my end. 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Sorry, I meant to ask whether you know how to detect whether the formatting is done within the axes section or have another alternative, as this is most likely a bug.

Hi @dpc_development ,

Please review the content in the following links, hope they can help you.

Dynamic Formatting of Switch Measures in Power BI

Finally! Format $ or % For the Same Measure On the Same Chart (..the Calculation Group bug fixed!)

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-yiruan-msft I had gone through those documents/videos, but they don't address the issue I am facing. I am already able to switch measures based on slicer selection and change the format using calculation groups. However, when using the measure in a chart, while the axis numbers show in a short neat manner - in millions or billions, the tooltip still shows in a full absolute manner.

I tried to avoid this by using formats such as '0,,.00', but that makes the visual format the axis numbers twice. So earlier when it showed 800mn on the left, now it shows 0.08mnM.

This seems to be a bug with Power BI.

 

Hence, I was hoping to find a solution to not format the calculation group when it is plotting numbers on the axes.

OwenAuger
Super User
Super User

Hi again,

Quite right, I see it's not actually working (I had misinterpreted what was happening in my test model) 😞

I will continue testing at my end.

 

Someone else out there may have some ideas...

Paging @tenfingers  🙂


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
OwenAuger
Super User
Super User

Hi @dpc_development 

It appears that the conflict in axis number formats can come about if you have both a format defined by a Calculation Group and the axis Display Units set to anything other than None. (i.e Auto, Millions, etc).

 

Try changing the axis Display Units setting to None. It seems to work for me in a test model.

Does that work for you?

 

Regards,

Owen


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

@amitchandak @parry2k have you faced something similar before?

 

Should this be reported as a bug instead?

@OwenAuger When I set the axes display units to 'None', it removes my calculation group formatting as well. I am using a Scatter Plot visual.

 

dpc_development_0-1642851962376.png

 

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.