Sum for group in table

[farsider]

I have a spreadsheet with a column that calculates the total for each key group and returns that total on each line of that column. I've use a array formula and this works well (slow), but now I'd like to do this in a column on PowerPivot.

I've been able to do this in series of measures, however I really need this in the table column. Example of working spreadsheet.

Sheet1

*	A	B	C
key	units	total
x
x
x
x
y
y
y
z
z
z
z

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]-2[/TD]
[TD="align: right"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="align: right"]-5[/TD]
[TD="align: right"]20[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="align: right"]15[/TD]
[TD="align: right"]20[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="align: right"]100[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="align: right"]50[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]

[TD="align: right"]-75[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]

[TD="align: right"]-75[/TD]
[TD="align: right"]0[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula C2 {=ROUND(SUM(IF($A2=INDIRECT("$a$2:$a$"&COUNTA(A:A)),INDIRECT("$b$2:$b$"&COUNTA(A:A)))),0)} <tbody> </tbody>

<tbody>
</tbody>

This formula is copied down the total column.

I've tried many different DAX formulas:

=CALCULATE(SUM([units]),[key]="Z", all(Data)) - this works for a single key

=CALCULATE(SUMx(data,Data[units]),DISTINCT(Data[key])) - not working.

Thanks

 

[StrafeXL]

I think this is what you're describing: Writing a Subtotal Calc Column, AKA The Simplest Use of the Earlier() Function « PowerPivotPro

 

[farsider]

Thanks StrafeXL - that's exactly what I needed. Also the link looks like a great source for PowerPivot.

Much appreciated