CODEWITHSUNDEEP
powerbidax
salaandora
salaandora

Reputation: 1

Why is my calculated column sum different than my natural column sum in DAX?

In my Power BI report I have a column for a Gross Margin Amount that is set to sum in visualizations, and I have a calculated column that calculates the sum of the GMA for each product. However, when I use them in a visualization, they are different. The code for the calculated column is:

SumMargin = 
VAR Margin = 'PRO vw_FactProfitability_Margin'[productRef]
RETURN
CALCULATE ( 
    SUM ( 'PRO vw_FactProfitability_Margin'[groupExtGrossMarginAmt] ),
    FILTER (
        'PRO vw_FactProfitability_Margin', 
        'PRO vw_FactProfitability_Margin'[productRef] = Margin
        )
    )

and the code that Power BI uses when you drag the original GMA column into a visualization is:

CALCULATE ( SUM ( 'PRO vw_FactProfitability_Margin'[groupExtGrossMarginAmt] ) )

To my understanding, these should be the same, but the calculated column's values are way different. This is the case for nearly every product, and I don't understand why. Could anyone offer some insight or advice? It would be greatly appreciated. Thank you in advance!

Upvotes: 0

Views: 202

Answers (1)

Himanshu Agrawal
Himanshu Agrawal

Reputation: 271

In order to get sum of amount by product you can create below measure:

SumMargin = 
CALCULATE ( 
    SUM ( 'PRO vw_FactProfitability_Margin'[groupExtGrossMarginAmt] ),
    FILTERS ('PRO vw_FactProfitability_Margin'[productRef]))

Creating a visual with SumMargin along with ProductRef will give you amount group by productRef

Upvotes: 1

Related Questions