Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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])))
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]
)
Proud to be a Super User!
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]
)
Proud to be a Super User!
@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])))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |