Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I can cumulate amount in one dimentsion, but now I would like to cumulate subtotals over dimensions in pivot table.
Example:
LOAD * INLINE [
Dept, SalesRep, ProductName, Amount
Dept1, Emp1, Product1, 200
Dept1, Emp1, Product2, 600
Dept1, Emp1, Product3, 100
Dept1, Emp2, Product2, 300
Dept1, Emp2, Product3, 900
Dept2, Emp3, Product1, 150
Dept2, Emp3, Product3, 450
Dept2, Emp4, Product1, 350
Dept2, Emp4, Product2, 300
Dept2, Emp4, Product3, 650
];
if dimension is Dept, SalesRep, ProductName, I can get subtotal of Dept, SalesRep and ProductName.
And I set cumulative of Amount, but it only show cumulative of dimension Dept, SalesRep and ProductName.
I want to get cumulative of Dept, SalesRep.
How can I do?
I have upload the example as attachement.
Thanks!
Hi,
Are you looking for something like:
RangeSum(above(total Sum(Amount), 0, RowNo(TOTAL)))
Within a pivot table, these functions can work unexpectedly (but logically!). Use of TOTAL can help here.
Regards,
Stephen