Forum Discussion

cheid_4838's avatar
cheid_4838
Icon for Helper IV rankHelper IV
1 year ago
Solved

Changing format when using measures in a slicer

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...
Need Help
  • danextian's avatar
    danextian
    1 year ago

    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's avatar
Ritaf1983
Icon for Super User rankSuper User
1 year ago

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's avatar
    cheid_4838
    Icon for Helper IV rankHelper IV
    1 year ago

    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
    )