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

     

7 Replies

  • 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 

  • cengizhanarslan's avatar
    cengizhanarslan
    Super User
    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"
)

     

    • cheid's avatar
      cheid
      Frequent Visitor
      5 months ago

      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.

       

       

      • danextian's avatar
        danextian
        Super User
        5 months ago

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

         

  • v-nmadadi-msft's avatar
    v-nmadadi-msft
    Community Support
    5 months ago

    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.