Variable format measure in a chart

[JeanMartinL]

Hello,

I already found a bunch of posts saying that what I'm trying to do is impossible but they are all a few years old so hopefully there is something new now to get around this issue.

Let's create the world's simplest table:

Let's say I want a single measure that depending on the category selected can return data in different formats, in my example Category 1 is a standard number, category 2 is a percentage.

I'll first create 2 intermediate measures for the individual values:

And then the global measure that switch between the 2.

This doesn't work, even though [PercentageData] is formatted as a Percentage, when used in [Data] it uses [Data]'s formatting option instead and returns the value as a number (expected behavior)

My solution to this was to use FORMAT in the [Data] measure:

This actually works fine for a Card or a Table, but doesn't work at all in any kind of chart:

My understanding of the issue is that FORMAT always return a text value, a datatype incompatible with the charts.

I tried a bunch of other things, like wrapping the expression in a VALUE or CONVERT but couldn't find a solution.

Is there any workaround that would enable me to show different formats from a single measure in a chart?

Thanks!

Caveat: I am using Power BI for Report Server January 2022, I'm not sure if the cloud version has the same behavior.

[JeanMartinL]

Ah very interesting, I learned about calculation groups a few weeks ago and I was looking for an opportunity to test them out, so this is perfect!

Thanks!

[DataInsights]

Excellent! Calculation groups are extremely useful, and can drastically reduce the number of measures in a data model, in addition to providing formatting flexibility.

[DataInsights · Accepted solution]

@JeanMartinL,

Calculation groups allow you to customize formatting without converting numbers to text (as the FORMAT function does). Here are some links to get you started. You'll need Tabular Editor (free utility) to create calculation groups.

https://www.sqlbi.com/articles/introducing-calculation-groups/ 

https://www.sqlbi.com/articles/controlling-format-strings-in-calculation-groups/