Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Dear All,
i have list of multiple category to calculate Weighted Averages, to calculate Weighted avg first i have to filter Month, SKU, then Traders In excel. i applied Sumproduct formula and i am getting exect Weighted Avg Number.
But when i tried to apply DAX Expression i am not getting the exact number.
Weighted Average =
DIVIDE (
SUMX ( 'Table', [SumMillion Ton] * [Rate Per] ),
SUMX ( 'Table', [Million Ton] )
)
Kindly help to resolve this query.
Thanks in Advance
Solved! Go to Solution.
please try
Weighted Average =
VAR CurrentTable =
CALCULATETABLE (
'Table',
ALLEXCEPT ( 'Table', 'Table'[Traders Name], 'Table'[SKU], 'Table'[Month] )
)
RETURN
DIVIDE (
SUMX ( CurrentTable, [SumMillion Ton] * [Rate Per] ),
SUMX ( CurrentTable, [Million Ton] )
)
Dear Tamerj,
Thanks for the update, As i have applied the fourmula which you provided but still i am not getting the exact value.
Hi @Nitin_Rajsoni
Can you please advise what is the formula that you have used in excel for the numerator and the denominator?
@tamerj1 To get value i am using Sumproduct formula- =SUMPRODUCT(MT*FSU)/SUM(MT)
@Nitin_Rajsoni
Apparently some of the provided information is no accurate. Are you sure you are filtering by month as well? As the sample screenshot does not say so. Please share the sample Excel file in order to verify exactly how the calculation is supposed to be handled. Thank you
Are you able to access now ?
@Nitin_Rajsoni
Still random! are you sure the DAX formula outcome is not correct?
In this report i am filtering the data Month follow with SKU then Importer name. Once will select the Importer name. then i applied the Sumproduct formula to find WA
eg : =SUMPRODUCT($P$5446:$P$5585,$I$5446:$I$5585)/SUM($I$5446:$I$5585)
Thanks for your support i will apply the same DAX Formula as you suggested.
Yes still their is some difference in WA, As suggested i have applied the below formula but number is not matching
User | Count |
---|---|
40 | |
26 | |
22 | |
16 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |