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 )

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
4 years ago

Hi,

if you want to use groupby function, please try the below. (attached file).

It is for creating a new table.

 

 

New table =
VAR newtable =
GROUPBY (
Data,
Data[Unit],
"@scoreweight", SUMX ( CURRENTGROUP (), Data[Score] * Data[Weight] ),
"@weightsum", SUMX ( CURRENTGROUP (), Data[Weight] )
)
RETURN
GROUPBY (
newtable,
Data[Unit],
"@calculatedColumn", SUMX ( CURRENTGROUP (), [@scoreweight] / [@weightsum] )
)