Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Using SUMX twice within GROUPBY expression

Unit

CategoryScoreWeight
Unit AX52
Unit AY103

Unit A

Z152

Unit B

X17

Unit B

Z29

Unit C

W411

 

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

 

UnitCalculated Column

Unit A

10
Unit B1.5625
Unit C4

 

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?
  • 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 )

5 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    4 years ago

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

  • smpa01's avatar
    smpa01
    Community Champion
    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 )

    • smpa01's avatar
      smpa01
      Community Champion
      4 years ago

      Anonymous Did you have a chance to look into this?

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super 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] )
    )

  • v-cazheng-msft's avatar
    v-cazheng-msft
    Community 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!