Forum Discussion
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.
8 Replies
- OwenAugerSuper User
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
- dpc_developmentHelper III
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_developmentHelper III
amitchandak parry2k have you faced something similar before?
Should this be reported as a bug instead?
- OwenAugerSuper 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 🙂
- parry2kSuper User
dpc_development since OwenAuger already tested it, but I will try to test at my end.
- dpc_developmentHelper III
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.
- AnonymousNot applicable
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