Measure invoking other measures with different output formats - Presentation in chart

[AlB]

Hi all,

We have a measure that calls on 3 other different measures depending on the selection of a slicer.

Slicer table

  Measure 1

  Measure 2

  Measure 3 

So the user can select the measure to apply by selecting its name in the slicer. The main measure code is then something like: 

MultiMeasure =
SWITCH (
    SELECTEDVALUE ( SlicerTable[Metric] ),
    "Measure 1", [Measure 1],
    "Measure 2", [Measure 2],
    "Measure 3", [Measure 3]
)

Then we use this [MultiMeasure] in a line chart, with date in the X axis for instance.

Now the problem is that [Measure 1] returns a large integer while [Measure 2] returns a percentage.

If we format [MultiMeasure] as a %, it will show nicely when the selected measure is [Measure 2] but not when [Measure 1] is selected. And viceversa if we format [MultiMeasure] as an integer. 

Does anyone know a way to get around this? Ideally we would want to have a sort of dynamic formatting for [MultiMeasure], so that it shows as % in the chart when the selection is Measure 2 and as a whole number when the selection is Measure 1

FORMAT cannot be use because it returns text and we are plotting in a chart, that requires numbers

Many thanks

@TomMartens @MFelix @Greg_Deckler 

[MFelix · Accepted solution]

Hi @AlB ,

You need to use the calculated groups check this post were I have made an explanation about it:

https://community.powerbi.com/t5/Desktop/How-to-dynamically-set-the-format-of-a-measure-in-a-Power-B...

[v-kelly-msft]

Hi @AlB ,

Try to modify your measure as below:

MultiMeasure =
SWITCH (
    SELECTEDVALUE ( SlicerTable[Metric] ),
    "Measure 1", [Measure 1],
    "Measure 2", FORMAT([Measure 2],"percent"),
    "Measure 3", [Measure 3]
)

And you will see:

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[AlB]

Hi @v-kelly-msft 

Thanks for your suggestion.

Your approach is not valid because it returns text. I need it to return a number, since the result goes into a chart. @MFelix  suggestion works great but I now have the problem that I described earlier; I already have another field in the legend of the chart.

Thanks

[MFelix · Accepted solution]

Hi @AlB ,

You don't need to use the field from the Calculated groups on the legend I just did that for example purposes if you want to show more than one measure, what you need to do is the basis measure to your chart add the legend values that you need and then use the Calculated group as a slicer on your report.

What is the type of chart you are using?

[AlB]

Thanks@MFelix

I'm using simple charts, either line or stacked column charts.

Ok, that makes sense. I'll try it out with the slicers from the calc group.

[MFelix]

In the tests I have made everything worked correctly. but again the data itself has you now may change the result.

Also check this post about formatting can give you some hints.

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

[MFelix · Accepted solution]

Hi @AlB ,

You need to use the calculated groups check this post were I have made an explanation about it:

https://community.powerbi.com/t5/Desktop/How-to-dynamically-set-the-format-of-a-measure-in-a-Power-B...

[AlB]

@MFelix 

Thanks a lot for your answer. In the example, you place the column from the calculated group table in the legend of the chart. I have another field that is being used in the legend and has to stay in the legend. Is there a work around for that? CAn it still be done if you need something else in the legend?

Many thanks