Forum Discussion
Matrix Subtotals Not Adding Up with Calculate Measure
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.
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.
Super User
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.
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.