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.

 

 

Revenue = sum(table[Total_Revenue])
 
MTD =
CALCULATE([Revenue],DATESMTD( 'FY Calender'[Date] ) )

 

 

.MTD formattedMeasure =
var _salesMeasure =
[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

 

 

FY Calender = CALENDARAUTO(6)
Month Year = month('FY Calender'[Date]) &"/"&YEAR('FY Calender'[Date])

 

 

 

 

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

     

1 Reply

  • DataInsights's avatar
    DataInsights
    Super 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