cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

How to dynamically set the format of a measure in a Power BI report?

I have a Power BI report with a measure that returns the value of other measures depending on the selected value of a slicer, as per the DAX below:

 

 

 

Slicer Measure = 
    SWITCH(
        LASTNONBLANK('Available Measures'[MeasureName], "")
        , "Sales", [Sales]
        , "Orders", [Orders]
        , "Orders Completed %", [Orders Completed %]
    )

 

 

This allows a user to select which measure they want to see in a line chart visual.

However, when the user selects Orders Completed %, I want the [Slicer Measure] to be formatted as a percentage. But I cannot work out a way to specify the measure format dynamically. 

I have tried formatting the Orders Completed % expression to a percentage text like this

 

 

Slicer Measure = 
    SWITCH(
        LASTNONBLANK('Available Measures'[MeasureName], "")
        , "Sales", [Sales]
        , "Orders", [Orders]
        , "Orders Completed %", FORMAT([Orders Completed %], "Percent")
    )

 

 

but when selected the Orders Completed % does not display anything at all.

Any suggestions on how to do this?

 

1 ACCEPTED SOLUTION

Hi @Anonymous ,

 

Even in live connection you can use calculation groups and create this changes, follow the step below:

  • Assuming you already have Sales Measure
  • On external tools execute the Tabular editor

MFelix_0-1602169947918.png

  • Rigth Click on Tables
  • New Calculation Group

MFelix_1-1602170020388.png

 

  • Rename as you need
  • Rigth click
  • New Calculation Item

MFelix_2-1602170081096.png

 

  • Rename it sales and write the folllowing code:
SELECTEDMEASURE()
  • Add two new calculation items with the formulas you have in the Power BI for the other two measures, if your measure in Power BI is for example: DIVIDE(SUM(Table[Column]), SUM(Table[Column2]) this is the formula you must place on the calculation item.
  • On the Percentage formula make your formatting as %

MFelix_3-1602170461284.png

 

  • Save the model

MFelix_4-1602170491710.png

 

  • Go back to Power BI and refresh the model you should see your new table in the model
  • Now create your line chart with the following setup:.
    • Axis: Column you need
    • Values: Measure Sales
    • Legend: Column from the calculated group table
  • Slicer: Column from calculated group table

Check result below:

 

Calculation_groups.gif

I cannot send you a copy of my file since it's on direct query.  But believe this can help you achieve the needed steps.


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

15 REPLIES 15
Fiala
Helper I
Helper I

HI @MFelix , I am very interested in your solution. But I don´t know how to use it in my case. I have two measure %utilization (percentage format) and AHT (number format). I need to create calculation group. I tried.. I have a slicer with % utilization and AHT and line chart. But POWER BI gives me this error: Can´t display the data because Power BI can´t determine the relationship between two or more fields. In tabular editor I just create two calculation item (% utilization and aht) with dax measures and set format. What I doing wrong please? Thank you

Hi @Fiala ,

 

When you have questions about relationships probably it's because the items you are using on your visualizations are from independent tables.

 

You refer that you have two measures and a slicer with the % percentage utilization and AHT, is the slicer created form the calculation groups or is it from another table?

 

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



nirmit27
Helper II
Helper II

Great post. Solved my requirement of dynamically changing the number format based on slicer selection (Euro / Hours)

MFelix
Super User
Super User

Hi @Anonymous ,

 

When you use the format option on a measure or calculated column the value that you apply the format becomes a text value so this is what is happening in your chart, that is why it does not show on the line chart since when it read the values it's a text and cannot be represent in the visualization.

 

In this case and since you cannot show values in percentage and values by using the switch measure I believe you have one of two options:

  • Make your final value multiplied by 100 and then place a card with the text values in % or Currency
  • Another option is to create bookmarks and 3 different charts

Check both in attach PBIX file.

 

 


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

To be able to do this THE RIGHT WAY you have to use calculation groups which can be created only via Tabular Editor. It can't be done easily via the PBI interface. But if you do it thorugh the groups it'll work exactly the way you want - the measure will be dynamically formatted on the fly and it'll still be a number, not a piece of text. For how to do this, please refer to YT or www.sqlbi.com.

Anonymous
Not applicable

We are still on version SQL 2016 which does not support calculation groups. Is there a way to achieve the same functionality in SSAS 2016?

Hi @Anonymous ,

 

You are using direct query mode?


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

The Power BI report connects to the SSAS database in "Connect Live" mode.

Hi @Anonymous ,

 

Following @Anonymous  answer check the video below that was publish today with this exact situation.

 


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

This video does not achieve what I have asked. The video shows measures being displayed as text values by converting the number to a text string and adding some more after the number. 

What I want to achieve is to dynamically change the way Power BI formats a numeric measure depending on a a slicer value.

 

Anonymous
Not applicable

@Anonymous 

 

You don't understand what the video really is all about. Alberto shows how to dynamically format a measure and the fact that he uses strings to add the unit to the number has nothing to do with this. Calculation groups let you format a measure's value DYNAMICALLY using the SELECTEDMEASUREFORMATSTRING() function. Try to watch this video once again and think about what Alberto does and how it can be used to format a number dynamically. Calculation items have properties and these properties can be set at runtime. One of the properties is the format string.

 

https://docs.microsoft.com/en-us/dax/selectedmeasureformatstring-function-dax

Anonymous
Not applicable

Perhaps it does, but the version of SSAS I am using is 2016, so this method is not available to me, unfortunately.

Hi @Anonymous ,

 

Even in live connection you can use calculation groups and create this changes, follow the step below:

  • Assuming you already have Sales Measure
  • On external tools execute the Tabular editor

MFelix_0-1602169947918.png

  • Rigth Click on Tables
  • New Calculation Group

MFelix_1-1602170020388.png

 

  • Rename as you need
  • Rigth click
  • New Calculation Item

MFelix_2-1602170081096.png

 

  • Rename it sales and write the folllowing code:
SELECTEDMEASURE()
  • Add two new calculation items with the formulas you have in the Power BI for the other two measures, if your measure in Power BI is for example: DIVIDE(SUM(Table[Column]), SUM(Table[Column2]) this is the formula you must place on the calculation item.
  • On the Percentage formula make your formatting as %

MFelix_3-1602170461284.png

 

  • Save the model

MFelix_4-1602170491710.png

 

  • Go back to Power BI and refresh the model you should see your new table in the model
  • Now create your line chart with the following setup:.
    • Axis: Column you need
    • Values: Measure Sales
    • Legend: Column from the calculated group table
  • Slicer: Column from calculated group table

Check result below:

 

Calculation_groups.gif

I cannot send you a copy of my file since it's on direct query.  But believe this can help you achieve the needed steps.


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi @MFelix 

Is this solution possible if your data is with import mode?

Also, I do not see the external tools tab in my ribbon. Do you know what could be the reason for that?

 

Hi @Quiny_Harl ,

 

Yes this option is possible also in import mode, for the external tools to work you need to install them it's

 

ALM Toolkit

DAX Studio

Tabular Editor


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!