Changing format when using measures in a slicer

[cheid_4838]

I have a visual where I have created a slicer to switch between different measures.  Some of the measures our percentages, dollars, and just decimal.  I tried changing the format, but it won't change.  How do I go about changing the format when using measures in a slicer so the format is reflected in the visual?  Thanks.

[danextian · Accepted solution]

hi @cheid_4838 

In the slicer tabe, add a column that indicates the format string. For example

Measure Name	Format String
Dollar Value	$#,#
Percentage	0.00%
Volume	#,#

Now, select the measure that switches between different measures. Go to format under the measure contextual tab. Click Dynamic from the dropdown.

Enter SELECTEDVALUE('slicer'[format string]) in the formula bar.

[Ritaf1983]

Hi @cheid_4838 
To dynamically change the format of your values based on the measure selected in your slicer, you can use a combination of SWITCH and a calculated DAX measure. While Power BI does not natively support dynamic format strings for measures in field parameters, you can simulate this by creating a measure for formatting your values.

Example DAX Measure
DAX
Copy code
FormattedMeasure =
VAR SelectedMeasure = SELECTEDVALUE('MeasureSelector'[MeasureName])
RETURN
SWITCH(
TRUE(),
SelectedMeasure = "Percentage Measure", FORMAT([YourPercentageMeasure], "0.00%"),
SelectedMeasure = "Currency Measure", FORMAT([YourCurrencyMeasure], "$#,##0.00"),
SelectedMeasure = "Decimal Measure", FORMAT([YourDecimalMeasure], "0.00"),
BLANK() // Default value if no measure is selected
)

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[cheid_4838]

I created this measure and entered the FORMAT in the return statement, but I am getting an error message shown below.  I even tried it with additional parenthesis and still didn't work.  Is FORMAT in the wrong location within the measure?

Allocated Tractor Cost =

--Calculate the Total Count for each slicer--

VAR TotalCountRouteID = CALCULATE(SUM('STOPS'[COUNT]), ALL('STOPS'[RouteID]))

VAR TotalCountStopID = CALCULATE(SUM('STOPS'[COUNT]), ALL('STOPS'[StopID]))

VAR TotalCountStopDescription = CALCULATE(SUM('STOPS'[COUNT]), ALL('STOPS'[StopName] ))

--Calculate the Line Distribution Count for each slicer--

VAR LineDistCountRouteID = DIVIDE([Line Count], TotalCountRouteID)

VAR LineDistCountStopID = DIVIDE([Line Count], TotalCountStopID)

VAR LineDistCountStopDescription = DIVIDE([Line Count], TotalCountStopDescription)

--Apply the percentage to the cost and operational numbers--

VAR AllocatedCostRouteID = CALCULATE(LineDistCountRouteID * sum(Invoice[Tractor Cost]))

VAR AllocatedCostStopID = CALCULATE(LineDistCountStopID * sum(Invoice[Tractor Cost]))

VAR AllocatedCostStopDescription = CALCULATE(LineDistCountStopDescription * sum(Invoice[Tractor Cost]))

--Create a measure to dynamically switch between the slicers--

RETURN

SWITCH(

TRUE(),

ISFILTERED('STOPS'[RouteID]), AllocatedCostRouteID,Format(AllocatedCostRouteID,"$#,##0.00"),

ISFILTERED('STOPS'[StopID]), AllocatedCostStopID, Format(AllocatedCostStopID,"$#,##0.00"),

ISFILTERED('STOPS'[StopName]), AllocatedCostStopDescription,Format(AllocatedCostStopDescription,"$#,##0.00"),

SUM(Invoice[Tractor Cost])  // Default case if no slicer is selected

)