Search code examples
excelpivotpivot-tabledaxpowerpivot

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?

Solution

  • 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