Why does my DAX measure not sum correct?

[JayJay0368]

Hi People,

I hope you can help me with a problem which is about to drive me insane:

I have this measure:

Netto ror distribution = 
VAR consFinal=0.0294
VAR SumVagtKorr=[weight]*(1/[SUM # where number is> 2,94%])*(170-[SUM # where number is 2,94%])
RETURN
IF([weight(Corrected)]=consFinal,5,SumVagtKorr)

And it is based on the following precalculations:

SUM # where number is 2,94% = 
SUMX ( 
        ALLSELECTED('Product'[Product Name]), 
        CALCULATE ( [# where number is 2,94%] , ALLEXCEPT ( 'Product' , 'Product'[Product Name] )
        ))

------------------------

SUM # where number is> 2,94% = 
SUMX ( 
        ALLSELECTED('Product'[Product Name]), 
        CALCULATE ( [# where number is >2,94%] , ALLEXCEPT ( 'Product' , 'Product'[Product Name] )
        ))

------------------------

vægt = DIVIDE(SUMX ( 
        VALUES('Product'[Product Name]), 
        CALCULATE ( [Sales<5] , ALLEXCEPT ( 'Product' , 'Product'[Product Name] )
        )),
        SUMX ( 
        ALLSELECTED('Product'[Product Name]), 
        CALCULATE ( [Sales<5] , ALLEXCEPT ( 'Product' , 'Product'[Product Name] )
        )))

-----------------------

Sales<5 = SUMX(VALUES('Product'[Product Name]), 
        CALCULATE ( IF([Sales]<5,5,[Sales]) , ALLEXCEPT ( 'Product' , 'Product'[Product Name] )
        ))

-----------------------------

SUM # where number is >2,94% = 
VAR ej_ror_u_5=IF([weight(Corrected)]>0.0294,[weight(Corrected)],0)
RETURN
CALCULATE ( ej_ror_u_5 , ALLEXCEPT ( 'Product' , 'Product'[Product Name] ))

----------------------------

SUM # where number is 2,94% = 
VAR ej_ror_u_5=IF([weight(Corrected)]=0.0294,5,0)
RETURN
CALCULATE ( ej_ror_u_5 , ALLEXCEPT ( 'Product' , 'Product'[Product Name] ))

I know it looks a bit "hairy", but all I need to know is why the calculation it adds up to doesn't sum the numbers correct??

Here is the result from the calculation (above):

Where the Corrrect number should be =171 :

Same numbers...different totals ??

Can anybody see why and if I can DAX my way to a correct sum?

Thanks.

Br,

Jayjay0306

[Anonymous · Accepted solution]

Hi @JayJay0368 ,

According to your statement, I know that your measures were calculated based on IF() function. I think this will cause the error in visual subtotal field.

I suggest you to create a new measure based on original [Netto ror distribution] measure.

Netto ror distribution with correct total =
SUMX (
    SUMMARIZE ( 'TableName', 'TableName'[Customer Name], 'TableName'[Product Name] ),
    [Netto ror distribution]
)

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[johnt75]

Watch https://www.youtube.com/watch?v=6rgAkejrup8 by SQLBI.