Matrix Subtotals Not Adding Up with Calculate Measure

[Anonymous]

Hello,

I created a measure to essentially replace values for a category in the visual (08&04 Reforecast) with values from another category (Actuals) if the year and month was less than or equal to 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.

Here is my DAX:

Measure =
SWITCH(
TRUE(),
MAX('PBIpull'[Type])="08&04 Reforecast" && MIN('PBIpull'[Year Month Code])>=202207,
CALCULATE(SUM('PBIpull'[Count]),'PBIpull'[Type]="08&04 Reforecast"),
MAX('PBIpull'[Type])="08&04 Reforecast" && MIN('PBIpull'[Year Month Code])<=202206,
CALCULATE(SUM('PBIpull'[Count]),'PBIpull'[Type]="Actuals"),
CALCULATE(SUM('PBIpull'[Count]))
)

My sample file: https://drive.google.com/file/d/17VKscN4qDQm1YS1G55nX-EzUioMjBTKq/view?usp=sharing

My PBI file: https://drive.google.com/file/d/1V_SpZW_HejtOe6Qd5v8f-RSYObEeDkss/view?usp=sharing

I'd appreciate any help with this. Thank you.

[Anonymous]

Thanks @lbendlin , I've attached my sample file above. My expected outcome is just that I need my matrix subtotals for the 08&04 Reforecast Measure to reflect the sum of the values in each year below which I'm not seeing currently as you can see in the screenshot above. Appreciate your help thank you. 

[lbendlin · Accepted solution]

Measure = 
var a = SUMMARIZE(PBIpull,[Area],[Sub-Area],[Year Month Code],[Type])
var b = ADDCOLUMNS(a,"sm",if([Type]="08&04 Reforecast" && [Year Month Code]<=202206, CALCULATE(SUM('PBIpull'[Count]),'PBIpull'[Type]="Actuals"),calculate(sum(PBIpull[Count]))))
return sumx(b,[sm])

I also disconnected your Calendar table and created a calculated column in the PBIpull table

Month Number = value(right([Year Month Code],2))

Try to avoid M:M bidirectional relationships.

[Anonymous]

That works thank you! Can I apply the same formula if the values that I need to change are from a measure instead of a column?

[lbendlin]

You need to try that out.  Usually the recommendation is to avoid nesting measures when possible.

[lbendlin]

Please provide sanitized sample data that fully covers your issue. I can only help you with meaningful sample data.
Please paste the data into a table in your post or use one of the file services like OneDrive or Google Drive. Screenshots of your source data are not useful.
Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523