Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 t...
Need Help
  • lbendlin's avatar
    lbendlin
    4 years ago

     

    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's avatar
Anonymous
Not applicable
4 years ago

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's avatar
lbendlin
Icon for Super User rankSuper User
4 years ago

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