topic Re: Using SUMX twice within GROUPBY expression in DAX Commands and Tips

Using SUMX twice within GROUPBY expression

Anonymous — Thu, 02 Dec 2021 23:29:26 GMT

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?

Re: Using SUMX twice within GROUPBY expression

wdx223_Daniel — Fri, 03 Dec 2021 00:26:21 GMT

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

Re: Using SUMX twice within GROUPBY expression

smpa01 — Fri, 03 Dec 2021 04:31:44 GMT

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 )

Re: Using SUMX twice within GROUPBY expression

Jihwan_Kim — Fri, 03 Dec 2021 04:37:56 GMT

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

Re: Using SUMX twice within GROUPBY expression

v-cazheng-msft — Tue, 07 Dec 2021 07:39:56 GMT

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!

Re: Using SUMX twice within GROUPBY expression

smpa01 — Tue, 07 Dec 2021 16:11:12 GMT

Anonymous Did you have a chance to look into this?