Row Value Formatting

[cheid]

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 put that measure in a row.  Is there another way to format those values?  Thanks.

Formatted Target Value =

VAR _SelectedKPI = SELECTEDVALUE(Target[Target])

VAR _Value = CALCULATE(SUM(Target[Target])

)

RETURN

SWITCH(

    _SelectedKPI,

    "TotalLoads", FORMAT(_Value, "#,#"),

    "Outbound", FORMAT(_Value, "#,#"),

    "Miles", FORMAT(_Value, "#,#"),

    "MiLoad", FORMAT(_Value, "#,#"),

    "DrvHours", FORMAT(_Value, "#,#.00"),

    "StandardMPH", FORMAT(_Value, "#,#.00"),

    "MPH", FORMAT(_Value, "#,#.00"),

    "StandardHrsLoad", FORMAT(_Value, "#,#.00"),

    "HrsLoad", FORMAT(_Value, "#,#.00"),

    "MSF", FORMAT(_Value, "#,#"),

    "MSFLoad", FORMAT(_Value, "#,#.0"),

    "LowMSF", FORMAT(_Value, "#,#"),

    "PctLowMSF", FORMAT(_Value, "0.0%"),

    "Fixed", FORMAT(_Value, "$#,#0"),

    "HourCharge", FORMAT(_Value, "$#,#0"),

    "DrvHourCharge", FORMAT(_Value, "$#,#0"),

    "Other", FORMAT(_Value, "$#,#0"),

    "FSC", FORMAT(_Value, "$#,#0"),

    "TotalCharge", FORMAT(_Value, "$#,#0"),

    "CostMSF", FORMAT(_Value, "$#,#0.00"),

    "CostLoad", FORMAT(_Value, "$#,#0.00"),

    "CostMile", FORMAT(_Value, "$#,#0.00"),

    "CostHour", FORMAT(_Value, "$#,#0.00"),

    "SpotHours", FORMAT(_Value, "#,#"),

    "SpotHourCharge", FORMAT(_Value, "$#,#0"),

    "SpotTractor", FORMAT(_Value, "$#,#0"),

    "TotalSpot", FORMAT(_Value, "$#,#0"),

    "GrandTotalCharge", FORMAT(_Value, "$#,#0"),

    "All In Cost/MSF", FORMAT(_Value, "$#,#0.00"),

    "CostMSF13", FORMAT(_Value, "$#,#0.00"),

    "ALL_IN_COSTMSF13",FORMAT(_Value, "$#,#0.00")

)

[v-nmadadi-msft]

Hi @cheid 

I wanted to check if you had the opportunity to review the information provided by @MasonMA . Please feel free to contact us if you have any further questions.

Thank you.

[cengizhanarslan · Accepted solution]

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"
)

[cheid]

I tried this, but the format logic won't save when I change to format and dynamic.  Is there a reason for this?

This is what happens when I hit enter to save the logic that was entered.

[MasonMA]

It looks the format logic references the wrong column (Target), instead of the KPI identifier (Measure).

Try using format logic based on the 'KPI' name and referencing SELECTEDVALUE('Table'[Measure]). 

[danextian]

You could create a separate measure and use that measure as format string. 

[cheid]

I have tried everyone's suggestions and nothing is working (see current state screenshot).  The only thing I haven't mentioned and I wasn't sure if this made a difference is under transformation I unpivot the main fact data table (see below) so I could create a relationship between fact table attributes and Target Measures.  I then was able to put the target values under "Rows" when I built the matrix (see current state table).  Can this make a difference when trying to format the target values?  Thanks.

Target Table

Main Fact Table

Relationship

Current State of Target Values

[MasonMA]

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