CODEWITHSUNDEEP
excelpivotpivot-tabledaxpowerpivot
Christoffer
Christoffer

Reputation: 347

Sum in pivot hierachy

I have the following DAX-formula to retrieve the opening and closing balance for a list of products.

 =CALCULATE(MAX(transactions[Balance]);
 FILTER(transactions;
 transactions[ID] = MAX(transactions[ID])
        )
     )

This works on row level in my Pivot but when I group this och Product category level I only get one value and not the sum of all the product rows.

My data contains of rows for each transaction and each row have a columns with current balance.

enter image description here

How do I sum each row to get the group sum for the above category "00-01" 26784 and 283500?

Upvotes: 1

Views: 49

Answers (1)

Marcus
Marcus

Reputation: 541

One way to do this is to leverage an iterative function like a SUMX.

Assuming that your EndValue is the measure that you defined.

SUMX_Example := SUMX( VALUES ( transactions[ID] ) , [EndValue] )

Which will do the following:

  1. Though VALUES ( transactions[ID] ) it will generate a list of your IDs
  2. For each ID it will run your already created [EndValue] measure
  3. Sum the result of each ID's end value

This is of course assuming [ID] does not cover categories. If ID does cross categories, then you would first do a SUMX using category, with another SUMX that does ID

Upvotes: 1

Related Questions