Measure returning Incorrect Totals

[Anonymous]

I am trying to calculate sales for a particular product class. I am using the below measure:

SumSales = SUMX(FILTER(DimProduct,DimProduct[Class]="L"),SUM(FactInternetSales[SalesAmount]))

 which is giving me a value of 3787269361.4703 (which is incorrect). However, I tried the below:

TotalSales = SUM(FactInternetSales[SalesAmount]) 

SumC = SUMX(filter(DimProduct,DimProduct[Class]="L"),[TotalSales]) 

which is giving me a value of 2133761.2238 (this is the correct one).

Can you anyone please explain me why the previous dax is not showing the correct total.

As per my knowledge, both DAX are same.

What am I missing?

Thanks in advance.

[amitchandak · Accepted solution]

@Anonymous , You can use calculate

refer: https://exceleratorbi.com.au/double-calculate-solves-sumx-problem/

SumSales = SUMX(FILTER(DimProduct,DimProduct[Class]="L"),calculate(SUM(FactInternetSales[SalesAmount])))

[danextian · Accepted solution]

Hi @Anonymous ,

In your first formula, what happens is that the total sales amount of rows where Class = L is multipled by the number of rows in table DimProduct wherein the value of Class column is L.  For example if the expected total value is 2133761.2238 and there are 1775 rows with Class L in DimProduct, the total then becomes 2133761.2238 * 1775

You may wrap your aggregation in CALCULATE as what @amitchandak has suggested or, alternatively, create a virtual table with SUMMARIZE.

SUMC =
SUMX (
    SUMMARIZE (
        DimProduct,
        DimProduct[Class],
        "value", IF ( DimProduct[Class] = "L", SUM ( FactInternetSales[SalesAmount] ) )
    ),
    [value]
)

[amitchandak · Accepted solution]

@Anonymous , You can use calculate

refer: https://exceleratorbi.com.au/double-calculate-solves-sumx-problem/

SumSales = SUMX(FILTER(DimProduct,DimProduct[Class]="L"),calculate(SUM(FactInternetSales[SalesAmount])))

[Anonymous]

Thanks for the solution!!