Incorrect multiplication in the pivot table

[stark___]

Colleagues, good afternoon.

I have a small problem that I cannot understand and need your advice.

We have a report of distributor sales of our products through their clients. The report shows only the quantity, and to evaluate the sale, we need to multiply the quantity and our prices. Actual prices are pulled from PriceBook, that created during the loading script.  The link between the report and the PriceBook through key: product name + date.

I am trying to create a pivot table that displayed the sales in monetary terms and there is a problem:

If I use sum (Quantity * Price), it turns out some nonsense (the result in the last column). Prices and the nomenclature are linked correctly, multiplication of columns Quantity and Price gives the correct result, the formula sum (Quantity) * Price,  also gives the correct result, but it does not show Total when minimized nomenclature.

I tried to make PriceBook based QVD, but the same result. The most interesting, if  PriceBook uploaded to Excel file and load back, sum (Quantity * Price), gives the correct result:

What am I doing wrong?

Best regards, Stark

[sunny_talwar · Accepted solution]

Try this:

Sum(Aggr(Sum(Quantity) * Price, AllYourChartDimensionsHere))

Make sure to replace AllYourChartDimensionsHere with all the dimensions you are using in the pivot table separated by comma

[stabben23]

Price is not aggregated on the same Level as Quantity, therefore you need to use aggr Down to lowest Level in Your pivot. All value needs an aggregatiojn if you want sum on top in pivot

[stark___]

Thank you,Sunny.

Your formula works perfect.

Best regards,Stark

[stark___]

Thank you, Staffan for your reply, but I have other question: why sum(Quantity * Price) works correct without aggregation, if I load PriceBook from Excel file?

Best regards, Stark

[stabben23]

Spooky, but maybe Excel do some aggregation for you, never mind if you have correct solution.