Using SUMX twice within GROUPBY expression

[Andrew986]

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 is, within each unit, add up the product of the scores and their corresponding weights, and divide by the sum of the weights within each unit. These should be displayed in a table. The output table should look like

Unit	Calculated Column
Unit A	10
Unit B	1.5625
Unit C	4

I have been fighting with this for hours and am pretty frustrated. 

Calculation = GROUPBY('Proper Business Unit Rollup Step 2 Table'[Unit],

"Calculated Column",

SUMX(

CURRENTGROUP(),

'Table'[Score]*'Table'[Weight])/SUMX(

        CURRENTGROUP(), Table[Score]

))

When I do this I get the following error:

No table scans other than introduced by GroupBy() function are allowed before CurrentGroup() functions.

It doesn't seem to like me putting two SUMX(CURRENTGROUP(),...) in the GROUPBY expression. How can I make this work?

[v-cazheng-msft]

Hi @Andrew986 

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!

[Jihwan_Kim]

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

[smpa01 · Accepted solution]

@Andrew986  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 )

[smpa01]

@Andrew986 Did you have a chance to look into this?

[wdx223_Daniel]

NewTable=ADDCOLUMNS(VALUES('Table'[Unit]),"@CalCol",CALCULATE(DIVIDE(SUMX('Table','Table'[Score]*'Table'[Weight]),SUM('Table'[Weight]))))