Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Matrix Table Total Not adding up Correctly

I am using a Matrix Table, but totals don't add up correctly.  How to add up each month as the total number with the .MTD formattedMeasure? I am open to changing my approach if needed.     ...
Need Help
  • DataInsights's avatar
    DataInsights
    1 year ago

    Anonymous,

     

    Try this measure that uses SUMX to iterate the Month Year values in the filter context.

     

    .MTD formattedMeasure =
VAR _salesMeasure =
    SUMX ( VALUES ( 'FY Calender'[Month Year] ), [MTD] )
VAR _result =
    SWITCH (
        TRUE (),
        ABS ( _salesMeasure ) > 1000000000, FORMAT ( _salesMeasure, "\$#,,,.0B;(\$#,,,.0B)" ),
        ABS ( _salesMeasure ) > 1000000, FORMAT ( _salesMeasure, "\$#,,.0M;(\$#,,.0M)" ),
        ABS ( _salesMeasure ) >= 1000, FORMAT ( DIVIDE ( _salesMeasure, 1000, 0 ), "$"".""#K;($"""",,#K)" ),
        ABS ( _salesMeasure ) > 0, FORMAT ( _salesMeasure, "\$"".""#,##0.00;(\$#,##0.00K)" ),
        ABS ( _salesMeasure ) = 0, FORMAT ( _salesMeasure, "0" )
    )
RETURN
    _result

     

DataInsights's avatar
DataInsights
Icon for Super User rankSuper User
1 year ago

Anonymous,

 

Try this measure that uses SUMX to iterate the Month Year values in the filter context.

 

.MTD formattedMeasure =
VAR _salesMeasure =
    SUMX ( VALUES ( 'FY Calender'[Month Year] ), [MTD] )
VAR _result =
    SWITCH (
        TRUE (),
        ABS ( _salesMeasure ) > 1000000000, FORMAT ( _salesMeasure, "\$#,,,.0B;(\$#,,,.0B)" ),
        ABS ( _salesMeasure ) > 1000000, FORMAT ( _salesMeasure, "\$#,,.0M;(\$#,,.0M)" ),
        ABS ( _salesMeasure ) >= 1000, FORMAT ( DIVIDE ( _salesMeasure, 1000, 0 ), "$"".""#K;($"""",,#K)" ),
        ABS ( _salesMeasure ) > 0, FORMAT ( _salesMeasure, "\$"".""#,##0.00;(\$#,##0.00K)" ),
        ABS ( _salesMeasure ) = 0, FORMAT ( _salesMeasure, "0" )
    )
RETURN
    _result