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.

     

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
4 years ago

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/1447523