topic Weighted Average DAX Expression in DAX Commands and Tips

Weighted Average DAX Expression

Anonymous — Tue, 16 Aug 2022 09:21:50 GMT

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

Re: Weighted Average DAX Expression

tamerj1 — Tue, 16 Aug 2022 04:34:34 GMT

Hi Anonymous

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] ) )

Re: Weighted Average DAX Expression

Anonymous — Tue, 16 Aug 2022 04:44:53 GMT

Dear Tamerj,

Thanks for the update, As i have applied the fourmula which you provided but still i am not getting the exact value.

Re: Weighted Average DAX Expression

tamerj1 — Tue, 16 Aug 2022 05:01:13 GMT

Hi Anonymous
Can you please advise what is the formula that you have used in excel for the numerator and the denominator?

Re: Weighted Average DAX Expression

Anonymous — Tue, 16 Aug 2022 05:04:46 GMT

tamerj1 To get value i am using Sumproduct formula- =SUMPRODUCT(MT*FSU)/SUM(MT)

Re: Weighted Average DAX Expression

tamerj1 — Tue, 16 Aug 2022 05:31:50 GMT

Anonymous
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

Re: Weighted Average DAX Expression

Anonymous — Tue, 16 Aug 2022 05:56:34 GMT

tamerj1 Data Sheet Kindly find the attcahed sheet for reference.

Re: Weighted Average DAX Expression

tamerj1 — Tue, 16 Aug 2022 05:58:56 GMT

Anonymous

Great. I'll look into it once I'm back to office.

Re: Weighted Average DAX Expression

tamerj1 — Tue, 16 Aug 2022 06:44:49 GMT

Hi Anonymous
I cannot download

Re: Weighted Average DAX Expression

Anonymous — Tue, 16 Aug 2022 06:55:38 GMT

Are you able to access now ?

Re: Weighted Average DAX Expression

tamerj1 — Tue, 16 Aug 2022 07:00:57 GMT

Anonymous
Yes. But There is no formula for WA Avg

Re: Weighted Average DAX Expression

Anonymous — Tue, 16 Aug 2022 09:07:48 GMT

tamerj1 Data As suggested i have added the formula in attcahed sheet.

Please Chk and let me know

Re: Weighted Average DAX Expression

tamerj1 — Tue, 16 Aug 2022 09:38:34 GMT

Hi Anonymous
This seems to me completely random

Re: Weighted Average DAX Expression

Anonymous — Tue, 16 Aug 2022 09:55:27 GMT

Data Set tamerj1 I have added the updated sheet for reference.

Please check and let me know

Re: Weighted Average DAX Expression

tamerj1 — Tue, 16 Aug 2022 10:40:35 GMT

Anonymous
Still random! are you sure the DAX formula outcome is not correct?

Re: Weighted Average DAX Expression

Anonymous — Tue, 16 Aug 2022 11:21:13 GMT

Yes still their is some difference in WA, As suggested i have applied the below formula but number is not matching

Weighted Average =

VAR CurrentTable =

CALCULATETABLE (

'Data 22',

ALLEXCEPT ( 'FData 22', 'Data 22'[IMPORTER NAME], 'Data 22'[SKU], 'Data 22'[MONTH], 'Data 22'[YEAR] )

)

RETURN

DIVIDE (

SUMX ( CurrentTable, [MT Vol] * [Rate Per FSU] ),

SUMX ( CurrentTable, [MT Vol] )

)

Re: Weighted Average DAX Expression

Anonymous — Tue, 16 Aug 2022 11:33:30 GMT

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)

Re: Weighted Average DAX Expression

tamerj1 — Tue, 16 Aug 2022 11:47:51 GMT

Anonymous
Then my original DAX formula should give the exct result.

Re: Weighted Average DAX Expression

Anonymous — Tue, 16 Aug 2022 12:35:00 GMT

Thanks for your support i will apply the same DAX Formula as you suggested.