Forum Discussion
Matrix Subtotals Not Adding Up with Calculate Measure
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.
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.
5 Replies
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-p/963216
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523Thanks 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.
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.