Inaccurate Matrix Subtotals with Multiple Measures

[Anonymous]

Hello,

I'm trying to build a matrix table that does two things simulataneously:

1. Change the values based on the any combination of currency selection(local or reporting currency (CAD)) and the overhead adjustment selection (multiple Sales by 1.35 if the department type is embedded)

2. Replace Forecast values with Actual values if the date is earlier than July 2022.

I was able to accomplish both 1. and 2. by first creating a measure using the switch function (called Measure1) and then I created a measure based on the results of Measure1 (called Measure2) to essentially replace Reforecast with Actual values if the year and month was less than 202207 without impacting the other category values. My measure seems to be working well as you can see in the screenshot below but my subtotals are displaying values from the Actuals instead of adding up the values within rows.

I had asked a similar question before but I wasn't able to apply the same type of formula when it came to a measure instead of a column. I'd really appreciate your help with this. Sample file and PBIX file below:

My sample file: https://docs.google.com/spreadsheets/d/1ACRHp4Atj1eOOJzeoW7QyYAxKtgagz3s/edit?usp=sharing&ouid=10592...

My PBI file: https://drive.google.com/file/d/13yXlu8owLw4wLbaTAUtePPXVXqgXMQMF/view?usp=sharing

[HoangHugo]

Hi

I can't not understand your measure and matrix, but if you want to sum vales within rows in subtotal, please try format: SUMX(SUMMARIZE(year column), your formula)

[Anonymous]

Hi @HoangHugo ,

What doesn't make sense to you? I tried your formula and the subtotals summed up properly when the type was Actuals but not when the type was reforecast. Screenshot with the DAX is below:

Measure3 = SUMX(SUMMARIZE('COMBINED ALL FINAL','COMBINED ALL FINAL'[Year]),

SWITCH( TRUE(),

MAX('COMBINED ALL FINAL'[Type])="Reforecast" && MIN('COMBINED ALL FINAL'[Year Month Code])<202207, CALCULATE([Measure1],'COMBINED ALL FINAL'[Type]="Actuals"),

CALCULATE([Measure1])))