CODEWITHSUNDEEP
powerbidaxpowerquery
Emmet Mc
Emmet Mc

Reputation: 21

DAX GROUPBY CURRENTGROUP

I have a table which is created as follows, which calculates 2 x measures - [SubTot Net Sales] and [SubTot Volume], however I wish to add a 3rd measure - being [SubTot Net Sales]/[SubTot Volume], however I can't find out how to accomplish this.

I have effectively tried DIVIDE(GROUPBY tbl_Act[Net Sales], GROUPBY tbl_Act[Volume] - (i.e same code as below) but I get error message:

"Function 'GROUPBY' scalar expressions have to be Aggregation functions over CurrentGroup(). The expression of each Aggregation has to be either a constant or directly reference the columns in CurrentGroup()."

Thoughts/Help much appreciated !!

Groupby = GROUPBY(
    tbl_Act,
    tbl_Act[Country],
    tbl_Act[Channel],
    tbl_Act[Customer],
    tbl_Act[Category],
    tbl_Act[Brand],
    tbl_Act[Pack],
    "SubTot Net Sales",
    SUMX(
        CURRENTGROUP(),
        tbl_Act[Net Sales]),
   "SubTot Volume",
   SUMX(
       CURRENTGROUP(),
       tbl_Act[Volume]))

Upvotes: 1

Views: 1765

Answers (1)

Jos Woolley
Jos Woolley

Reputation: 9052

Just use ADDCOLUMNS:

Groupby =
ADDCOLUMNS(
    GROUPBY(
        tbl_Act,
        tbl_Act[Country],
        tbl_Act[Channel],
        tbl_Act[Customer],
        tbl_Act[Category],
        tbl_Act[Brand],
        tbl_Act[Pack],
        "SubTot Net Sales", SUMX( CURRENTGROUP(), tbl_Act[Net Sales] ),
        "SubTot Volume", SUMX( CURRENTGROUP(), tbl_Act[Volume] )
    ),
    "SubTol Net Sales/Volume", DIVIDE( [SubTot Net Sales], [SubTot Volume] )
)

Upvotes: 1

Related Questions