Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Using SUMX twice within GROUPBY expression

Unit Category Score Weight Unit A X 5 2 Unit A Y 10 3 Unit A Z 15 2 Unit B X 1 7 Unit B Z 2 9 Unit C W 4 11   Essentially, what I want to do i...
  • smpa01's avatar
    smpa01
    4 years ago

    Anonymous  try this 

     

    Measure =
    VAR _sum =
    SUMX ( tbl, tbl[score] * tbl[weight] )
    VAR _count =
    CALCULATE ( SUM ( tbl[weight] ), ALLEXCEPT ( tbl, tbl[unit] ) )
    RETURN
    DIVIDE ( _sum, _count )

v-cazheng-msft's avatar
v-cazheng-msft
Icon for Community Support rankCommunity Support
4 years ago

Hi Anonymous 

 

You may try this Calculated Column.

Calculated Column =

DIVIDE (

    'Table'[Score] * 'Table'[Weight],

    CALCULATE ( SUM ( 'Table'[Weight] ), ALLEXCEPT ( 'Table', 'Table'[Unit] ) )

)

 

The result should look like this:

 

For more details, please refer to the attached pbix file.

 

Best Regards,

Community Support Team _ Caiyun

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If you still have problems on it or I misunderstand your needs, please feel free to let us know. Thanks a lot!