cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Post Patron
Post Patron

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 @EylesIT ,

 

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

12 REPLIES 12
Super User III
Super User III

Hi @EylesIT ,

 

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





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.

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 @EylesIT ,

 

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





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

Hi @EylesIT ,

 

Following @daxer  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





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.

 

@EylesIT 

 

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

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

Hi @EylesIT ,

 

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

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
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors