Matrix Table Total Not adding up Correctly

[Anonymous]

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

[DataInsights · Accepted solution]

@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