Forum Discussion
Advocate IIDynamic formatting isn't working as expected for data label details
I need to change the number format to show 'MM' for millions and 'M' for thousands (instead of the default 'M' and 'K').
To achieve this, I'm using the following dynamic format:
VAR CurrentValue = SELECTEDMEASURE()
RETURN
SWITCH(
TRUE(),
CurrentValue <= 1E3, "#,0.0",
CurrentValue <= 1E6, "#,0,.0M",
CurrentValue <= 1E9, "#,0,,.0MM"
)Unfortunately, I've found that this doesn't work correctly for data labels. I have data labels turned on for both 'Actuals' and 'Plan,' and, as you can see, the dynamic formatting works as expected there. However, when I add 'Plan' as a data label detail for 'Actuals,' the units disappear entirely, as if the dynamic format isn't being applied at all.
Does anyone know why this is happening? Is this a known issue, or am I missing something?
6 Replies
speedrampsSuper User
Answer = VAR CurrentValue = SUM(yourdata[Sales]) VAR formatstring = SWITCH( TRUE(), CurrentValue <= 1E3, "#,0.0", CurrentValue <= 1E6, "#,0,.0M", CurrentValue <= 1E9, "#,0,,.0MM" ) RETURN FORMAT(CurrentValue,formatstring)Please click thumbs up just for the helpful suggestion. 👍
Then click [accept solution] it is works.
Thank you 😀
- mrbartuss
Thank you for that. However in a simple table even my dynamic format solution works. The problem is that id does not, for example, for data label details.
So you're suggesting I should duplicate all measures with FORMAT function?
- speedramps
Do you know how to use calculation group?
You could use calculation group to format mesures with SELECTED(measures)
Lean how herehttps://learn.microsoft.com/en-us/power-bi/transform-model/calculation-groups
- mrbartuss
Are you suggesting creating a calculation item with SELECTEDMEASURE() and applying the dynamic format to it? Wouldn't this format all measures the same way? How would it handle measures that should be formatted as percentages or other formats?
- speedramps
Do you know how to create a Field Parameter?
Learn how here
https://learn.microsoft.com/en-us/power-bi/create-reports/power-bi-field-parameters
You can create a field parameter with lots of measure
some intergers some currency and some percentages
The group these into categories intergers, currency and percentages.
This will add the category column to the field paramater table.
You can then use that column in then calculatation group with
SELECTEDMEASURE() and a SELECTEVALUE to determin the file parameter categoryand apply the approriate fomatting logic.
Thus simulating the dynamic behaviour.However, field paramters are not supported and have known issue.
See limitation in documenation
Hi mrbartuss
Has your problem been resolved? If so, could you mark the corresponding reply as the solution so that others with similar issues can benefit from it?
Best Regards,Jayleny
