CODEWITHSUNDEEP
powerbidaxpowerbi-desktop
Strawberryshrub
Strawberryshrub

Reputation: 3379

Group by and then sum value

I am struggling to get this going and could need some help. I have the following setup:

Order     Item      Material     Value
 22        1          100         27,5
 22        1          200         27,5
 22        1          300         27,5
 22        2          100         33
 22        3          500        101
 26        1          500         88
 26        1          600         88

I have duplicate values becaue of the Material, so I want to group by Order, Item and Value and then calculate the total Value in a DAX measure.

After grouping:

Order     Item     Value
 22        1        27,5
 22        2        33
 22        3       101
 26        1        88

The final Value:

Total Measure = 249,5

I tried the following DAX expression for the Total Measure:

Total Measure = Summarize('Table1'; 'Table1'[Order]; 'Table1'[Item]; "Sum Value:"; Sum('Table1'[Value]))

It gives me the error:

Multiple columns cannot be converted to a scalar value

So I tried:

Total Measure = Sumx('Table1'; Summarize('Table1'; 'Table1'[Order]; 'Table1'[Item]; "Sum Value:"; Sum('Table1'[Value])))

But this didnt work either. For every help thanks in advance.

Upvotes: 1

Views: 3543

Answers (2)

JSBY
JSBY

Reputation: 131

The following code should be what you are looking for

Measure1 =
SUMX (
    SUMMARIZE (
        Table1;
        Table1[Order];
        Table1[Item];
        Table1[Value];
        "TotalSum"; SUM ( Table1[Value] )
    );
    [Value]
)

Upvotes: 2

Alexis Olson
Alexis Olson

Reputation: 40204

In this case, you can simply use the VALUES function instead of SUMMARIZE.

Total Measure = SUMX ( VALUES ( Table1[Value] ), [Value] )

This iterates over each unique Value and adds Value to the sum.

Upvotes: 0

Related Questions