Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 g...
  • tamerj1's avatar
    tamerj1
    4 years ago

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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Are you able to access now ?

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

Anonymous 
Yes. But There is no formula for WA Avg

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

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

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

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

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

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

     

    Please Chk and let me know 

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi Anonymous 
    This seems to me completely random

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

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

    Please check and let me know  

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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] )
    )
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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)