Getting correct subtotals and grand total

[JEB85]

Hi Guys,</SPAN>

I have a data structure within powerpivot as follows:</SPAN></SPAN>

[TABLE="width: 281"]
<TBODY>[TR]
[TD]Product[/TD]
[TD]Period[/TD]
[TD]Price[/TD]
[TD]Quantity[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]8.5[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]250[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]450[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]250[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]12.5[/TD]
[TD="align: right"]75[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]200[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]500[/TD]
[/TR]
</TBODY><COLGROUP><COL span=2><COL><COL></COLGROUP>[/TABLE]

I’ve created a measure to calculate Revenue which is simply </SPAN>=sum(Table1[Price])*sum(Table1[Quantity])</SPAN></SPAN>

When I pivot the data I get the following:</SPAN></SPAN>

[TABLE="width: 352"]
<TBODY>[TR]
[TD]Revenue[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product[/TD]
[TD]Period[/TD]
[TD]Price[/TD]
[TD]Quantity[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]1[/TD]
[TD]8.5</SPAN>[/TD]
[TD]100</SPAN>[/TD]
[TD="align: right"]850.0</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2[/TD]
[TD]5</SPAN>[/TD]
[TD]250</SPAN>[/TD]
[TD="align: right"]1,250.0</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3[/TD]
[TD]3</SPAN>[/TD]
[TD]450</SPAN>[/TD]
[TD="align: right"]1,350.0</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]4[/TD]
[TD]5</SPAN>[/TD]
[TD]250</SPAN>[/TD]
[TD="align: right"]1,250.0</SPAN>[/TD]
[/TR]
[TR]
[TD]A Total[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]22,575.0[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]1[/TD]
[TD]12</SPAN>[/TD]
[TD]100</SPAN>[/TD]
[TD="align: right"]1,200.0</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2[/TD]
[TD]12.5</SPAN>[/TD]
[TD]75</SPAN>[/TD]
[TD="align: right"]937.5</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3[/TD]
[TD]8</SPAN>[/TD]
[TD]200</SPAN>[/TD]
[TD="align: right"]1,600.0</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]4[/TD]
[TD]6</SPAN>[/TD]
[TD]500</SPAN>[/TD]
[TD="align: right"]3,000.0</SPAN>[/TD]
[/TR]
[TR]
[TD]B Total[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]33,687.5[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]115,500.0[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL><COL></COLGROUP>[/TABLE]

The subtotals and grand total are incorrect.</SPAN></SPAN>

A’s Total should be 4,700</SPAN></SPAN>
B’s Total should be 6,737.5</SPAN></SPAN>
Therefore the grand total should be 11,437.5</SPAN></SPAN>

Can anyone please advise on how I can get these values?</SPAN></SPAN>

Thanks</SPAN></SPAN>

 

[Jeffrey Lopez]

Might want to post this over in the PowerPivots Forum... wish I knew more about PowerPivots to help, but you might get a quicker answer there.

 

[kapitan]

hi
********>alert('hh')*********>

 

[miguel.escobar]

Instead of using SUM() * SUM() you should try using SUMX
=SUMX( Table1, Table1[Quantity] * Table1[Price])

 

[JEB85]

Thanks miguel!!