Forum Discussion

cheid's avatar
cheid
Frequent Visitor
5 months ago
Solved

Row Value Formatting

I created the below matrix where I put the target values as a row value.  The problem I am having is that I can't format the values.  I tried creating a switch statement with formatting, but I can pu...
Need Help
  • cengizhanarslan's avatar
    cengizhanarslan
    5 months ago

    1) Keep your measure returning the numeric value only

    Target Value =
SUM(Target[Target])

     

    2) Create a format expression for the measure

    In Model view → select the measure → Format → Dynamic, use something like:

    VAR _SelectedKPI = SELECTEDVALUE(Target[Target])
RETURN
SWITCH(
    _SelectedKPI,
    "TotalLoads", "#,#",
    "Outbound", "#,#",
    "Miles", "#,#",
    "MiLoad", "#,#",
    "DrvHours", "#,#.00",
    "StandardMPH", "#,#.00",
    "MPH", "#,#.00",
    "StandardHrsLoad", "#,#.00",
    "HrsLoad", "#,#.00",
    "MSF", "#,#",
    "MSFLoad", "#,#.0",
    "LowMSF", "#,#",
    "PctLowMSF", "0.0%",
    "Fixed", "$#,#0",
    "HourCharge", "$#,#0",
    "DrvHourCharge", "$#,#0",
    "Other", "$#,#0",
    "FSC", "$#,#0",
    "TotalCharge", "$#,#0",
    "CostMSF", "$#,#0.00",
    "CostLoad", "$#,#0.00",
    "CostMile", "$#,#0.00",
    "CostHour", "$#,#0.00",
    "SpotHours", "#,#",
    "SpotHourCharge", "$#,#0",
    "SpotTractor", "$#,#0",
    "TotalSpot", "$#,#0",
    "GrandTotalCharge", "$#,#0",
    "All In Cost/MSF", "$#,#0.00",
    "CostMSF13", "$#,#0.00",
    "ALL_IN_COSTMSF13", "$#,#0.00"
)

     

MasonMA's avatar
MasonMA
Super User
5 months ago

Instead of using FORMAT(), keep the measure numeric and control formatting through a dynamic format string.

To do this, in Model view select the measure, and Dynamic in 'Format'.

 

Use your DAX to replace the highlighted script. 

SWITCH(
SELECTEDVALUE(Target[Target]),
"TotalLoads", "#,#",
"Outbound", "#,#",
"Miles", "#,#",
"MiLoad", "#,#",
"DrvHours", "#,#.00",
"StandardMPH", "#,#.00",
"MPH", "#,#.00",
"StandardHrsLoad", "#,#.00",
"HrsLoad", "#,#.00",
"MSF", "#,#",
"MSFLoad", "#,#.0",
"LowMSF", "#,#",
"PctLowMSF", "0.0%",
"Fixed", "$#,#0",
"HourCharge", "$#,#0",
"DrvHourCharge", "$#,#0",
"Other", "$#,#0",
"FSC", "$#,#0",
"TotalCharge", "$#,#0",
"CostMSF", "$#,#0.00",
"CostLoad", "$#,#0.00",
"CostMile", "$#,#0.00",
"CostHour", "$#,#0.00",
"SpotHours", "#,#",
"SpotHourCharge", "$#,#0",
"SpotTractor", "$#,#0",
"TotalSpot", "$#,#0",
"GrandTotalCharge", "$#,#0",
"All In Cost/MSF", "$#,#0.00",
"CostMSF13", "$#,#0.00",
"ALL_IN_COSTMSF13", "$#,#0.00"
)

 

For more details please refert to Digging into DYNAMIC format strings for DAX Measures in Power BI