cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Nitin_Rajsoni
Frequent Visitor

Weighted Average DAX Expression

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. 

Nitin_Rajsoni_0-1660623000260.png

Weighted Average =

DIVIDE (

SUMX ( 'Table', [SumMillion Ton] * [Rate Per] ),
SUMX ( 'Table', [Million Ton] )
)   

Kindly help to resolve this query.

 

Thanks in Advance  

1 ACCEPTED SOLUTION

@Nitin_Rajsoni 
Then my original DAX formula should give the exct result.

View solution in original post

18 REPLIES 18
tamerj1
Super User
Super User

Hi @Nitin_Rajsoni 

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

 

@tamerj1 Data Sheet Kindly find the attcahed sheet for reference.

 

 

Hi @Nitin_Rajsoni 
I cannot download 

Are you able to access now ?

@Nitin_Rajsoni 
Yes. But There is no formula for WA Avg

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

 

Please Chk and let me know 

Hi @Nitin_Rajsoni 
This seems to me completely random

1.png

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

Please check and let me know  

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

1.png

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)

Nitin_Rajsoni_0-1660649437611.png

 

  

@Nitin_Rajsoni 
Then my original DAX formula should give the exct result.

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  

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

@Nitin_Rajsoni 

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

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show Episode 10 Recap

The Power BI Community Show

Watch the playback when Amit Chandak, a Power BI Super User, demos how to use Field Parameters to make reports more dynamic.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.

Top Solution Authors
Top Kudoed Authors