cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate III
Advocate III

Dynamic formatting of measures - tutorial

Hi everyone - I would like to share how to use dynamic formatting on measures in Power BI.

This has already been available by using FORMAT DAX function, however that function converts measures to text and so it could not be used on most visuals.

 

Here is the final result that we will get. With a selection of a measure the formatting will change (2 decimal places for Value and 0 for Volume):

RafalK_0-1597435473380.pngRafalK_1-1597435483621.png

I will be working on a simple sales table with data:

RafalK_2-1597435551211.png

 

Tutorial - step by step:

1. Create a table with measures that you would like to select on the report. Add additional column that describes the desired measure format (you can use any format string that you would normaly use in the FORMAT() DAX function)

RafalK_4-1597435708673.png

 

2. Create a Dynamic measure with switch. 

 

RafalK_3-1597435616132.png

 

3. Add a slicer and add your Measure column to it so that we can select the measure we wish to see. Select a random value in the slicer.

11.png

 

4. Create a visual and add a desired Dimension (in my case Country) and Dynamic Measure as Value.

10.png

 

You will now be able to swap the measure on the graph based on the selection from the slicer but both measures still have the same format. 

 

5. Now we get to the hard part. You will need to add a Calculation Group using Tabular Editor.

You need to download and install it and then you will see it on the "External Tools" ribon in Power BI. 

RafalK_5-1597435824403.png

Open Tabular Editor and create a new Calculation Group. I have called mine Dynamic formatting.

Add new Calculation Item. I have called mine Format Measure.

In the Expression Editor add SELECTEDMEASURE()

In Format String Expression add SELECTEDVALUE('Measure'[Format], SELECTEDMEASUREFORMATSTRING())

This will take the measure used on the visual and format it by the definition from 'Measure'[Format]

Annotation 2020-08-14 221446.png

 

Click Save Changes and return to Power BI

 

6. Include Dynamic formatting in the graph. You can do it in two ways:

a. Add the Name from Dynamic formatting to graph legend (I had some problems with graph colors with this aproach)

3.png

b. Add Name from Dynamic formatting to visual filters and just select the only value

4.png

 

That's it. 

 

Regards

Rafał Kun
5 REPLIES 5
Highlighted
Frequent Visitor

Great idea, thank you for sharing with us! 🙂

Highlighted
Helper II
Helper II

Do you have the .pbix for this? I'm trying something very similar but I'm not sure if it's going to work and my dataset is very large making experimentation on it extremely difficult. Thanks. 

Highlighted
Helper I
Helper I

Hello, Thank you for the guide, it helped me with a visual where I was using a Dynamic Measure.

But....

It seems to have an effect on a lot of other visuals as well. Some measures suddenly take a different format then what I set it to.

Those are measures that have nothing to do with the ones I made it for. Sorting options dont work properly anymore because of it.

When I remove the Dynamic Formating table, it goes back to normal.

 

Simple example, I have a different measure that is a Percentage, but as a Gauge it simply won't show it as a percentage. In a matrix or table, it shows it as percentage, but not as a gauge.

 

How do i make it so that the Dynamic Formatting only works for the Dynamic Measure?

Highlighted

Hi - yea there are some problems with the method. 

For 1 it works best if you have just one measure on the visual. If you add more they will all be formated.

As for your Gauge I'm not sure wht it is the case. Perpaps the format you are using is not the same "percentage" format as the visual requires? All I can suggest here is to use the original measure without the dynamic format option.

Rafał Kun
Highlighted

Hi thanks for the reply.

 

Oh well it still helps knowing there is a work around 🙂 

Hope they will add something in the future so you can use FORMAT and that it wont be text or something

Thanks for the guide!

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors