Totals Row not summing properly

[mclawler]

Hi Everybody, I can't seem to figure out how to get the Totals row to Sum properly.  I would assume SUMX needs to be interjected in here somewhere but can't quite seem to fogure it out.  Please advise.  

Current Measure being used:

OB COUNT(Copy) =

VAR AutoCount=CALCULATE(SUM('OutstandingBalancesMaster'[Count])) + CALCULATE(COUNT('PwBi CIPL'[Balance]),OutstandingBalancesMaster[Type Description Column] = "Auto")

VAR OtherCount=COALESCE(

CALCULATE(SUM(OutstandingBalancesMaster[Count]),ALLEXCEPT('OutstandingBalancesMaster',OutstandingBalancesMaster[Type Description Column])),0)

VAR C=CALCULATE(SUM('OutstandingBalancesMaster'[Count]),FILTER('OutstandingBalancesMaster',OutstandingBalancesMaster[Type Description Column] = "Auto"))

RETURN

IF(

    ISINSCOPE(OutstandingBalancesMaster[Type Description Column]),

    SWITCH(

        TRUE(),

        MAX(OutstandingBalancesMaster[Type Description Column]) = "Auto",

        AutoCount,

        OtherCount),

        AutoCount+OtherCount-C) + 0

The individual row results are correct, just the Total is incorrect. 

# Total should be 23,917 

$ Total should be $514,771,284

It appears to be doubling all rows sums except for Auto

[MattAllington · Accepted solution]

The simplest way to solve the "totals don't add up" problem is to replicate the structure of the rows in your visual inside the measure. 

the reason you are getting the right answer in the row section is because each row is being calculated separately. At the total row, all of the data is aggregated and a single calculation is completed. You need to replicate the row by row calculation in the total. 

write a new measure that references the current measure

SUMX(VALUES(Table[Column in your visual]),[reference current measure that doesn't work])

or rewrite the current measure 

SUMX(VALUES(Table[Column in your visual]),CALCULATE(<all code in the current measure that doesn't work>)

https://exceleratorbi.com.au/use-sum-vs-sumx/

[mclawler]

That worked!! Also so easy in hindsight 🙂  

I wrote a new measure that referenced the old.  I tried the rewriting method and couldn't get it to foot correctly.  Thank you!!!