Dynamic Formatting of Measure (Without changing type to text)

[mbohling]

Hello,

I am looking to allow users of my report to change visuals using a slicer from Sales (Dollars in currency $) to Quantity (Whole number).

I have the functionality working using a separate table called "Metrics" and the following DAX calculated measure formula:

Selected Metric =

IF (

    HASONEVALUE ('Metrics'[Metric]),

    SWITCH (

        VALUES ('Metrics'[Metric]),

        "Dollars", SUM(Sales[Dollars]),

        "Quantity", SUM(Sales[Quantity])

    ),

    SUM(Sales[Dollars])

)

However, I can only choose one number format for the result in the visual.  If I try to format the results in the visual using the FORMAT() DAX function, it changes the number to text and then cannot be used in bar graphs/line graphs etc...

I am aware of the calculated groups functionality using the external tool Tabular Editor, but I cannot get it to work using my current set up.

Ultimately I want the current functionality of using a slicer to choose "Dollars" or "Quantity" which changes the resulting numbers in the visuals, but in the CORRECT format of for example: $123.23 to 83

Any help would be greatly appreciated!

[mbohling · Accepted solution]

So I was able to figure this out.  I followed this blog post: https://rollingaverage.com/set-up-measure-selection-with-dynamic-formatting-in-powerbi/

Essentially, I just needed to replace my separate table called Metrics with the Calculation Group.  In Tabular Editor 3, I created a Calculation Group called Metric with two Calculation Items, Sales and Quantity.  The DAX expression for both was SELECTEDMEASURE() and then I changed the Format String Expression for each as follows:

For Sales, "$#,0.00"

For Quantity, "#,0"

Finally, I changed my previous Calculated Measure to:

Selected Metric =

    SWITCH (

        SELECTEDVALUE ( 'Metric'[Selected] ),

        "Sales", SUM( Sales[Dollars] ),

        "Quantity", SUM( Sales[Quantity] )

    )

And that did it.  It was then just a matter of adding the calculated Measure 'Selected Metric' to any of my visuals and then adding a Slicer to change between the Metric from my Calculation Group between Sales and Quantity.

The measure changes AND the format is now correct.

[amitchandak]

@mbohling , in place of the above measure use field parameter with two measures you have

Power BI Field Parameters — A Quick way for Dynamic Visuals: https://amitchandak.medium.com/power-bi-field-parameters-a-quick-way-for-dynamic-visuals-fc4095ae9af...
Power BI Field Parameters- Measure Slicer and Axis/Dimension slicer: https://youtu.be/lqF3Wa1FllE
Switch TOPN with Field Parameters: https://amitchandak.medium.com/switch-topn-with-field-parameters-299a0ae3725f
Field Parameters- Conditional Formatting
: https://amitchandak.medium.com/field-parameters-conditional-formatting-517aacc23fdf