Calculating the Grand Total correctly

[datiduke]

Hi,
I have been working on this for a couple of weeks now and have come quite far. But there is this little piece at the end, which isn't working...

Scenario:
From one FY to another, you get a difference in profit.
A change in profit can either come from: Change in volume, change in retail price, change in discounts, or change in cost price.
If you break it down by product and unit values, you can compare it between the two FinYears.

Have a look here:
https://drive.google.com/open?id=0B9F3mj-MPt1dc2IxM0RzUzZRZ00

You can see, the total profit change was -$207.48, which is caused by P1 of -$149.26, P2: -$111.93 and P3: $53.72 (I haven't mentioned the sums in the picture...)

If we want to know now why it changed, we can see we lost $-694.81 because we have sold less units. We made $249.31 because we have given less discounts, We had to pay more for our products (increase in cost of $518.06), but at the same time we also increased the retail price, a little bit more though which resulted in a higher profit of ($756.08).

Now here is the problem. The Grand Total shows different! Here are the formulas I used for:
Volume: IF(ISBLANK([Qty PY])=TRUE(),[Profit ea]*SUM(Sales[Quantity]),[Volume Change]*[Profit ea PY])

Discount: IF(ISBLANK([Qty PY])=TRUE(),0,((SUM(Sales[Discount_ex_gst])/SUM(Sales[Quantity]))-(CALCULATE(SUM(Sales[Discount_ex_gst])/SUM(Sales1[Quantity]),SAMEPERIODLASTYEAR(dim_date[calendar_date]))))*SUM(Sales[Quantity])*-1)

Cost: IF(ISBLANK([Qty PY])=TRUE(),0,((SUM(Sales[Cost])/SUM(Sales[Quantity]))-(CALCULATE(SUM(Sales[Cost])/SUM(Sales[Quantity]),SAMEPERIODLASTYEAR(dim_date[calendar_date]))))*SUM(Sales[Quantity])*-1)

Retail: IF(ISBLANK([Qty PY])=TRUE(),0,((SUM(Sales[Retail_ex_gst])/SUM(Sales[Quantity]))-(CALCULATE(SUM(Sales[Retail_ex_gst])/SUM(Sales[Quantity]),SAMEPERIODLASTYEAR(dim_date[calendar_date]))))*SUM(Sales[Quantity]))

I assume the way it is calculating is first sum the values for all products, but it should first divide the value by qty and then sum it.

So, appreciate any help you have...
Cheers,
Andy

 

[bendipa]

In plain English what is 'Pdt' supposed to be (shown in last column), and how is it calculated? What totals are you exactly trying to show or summarize?

 

[datiduke]

In plain English what is 'Pdt' supposed to be (shown in last column), and how is it calculated? What totals are you exactly trying to show or summarize?

Pdt ist just "Profit due to"... Qty/Volume, Cost, Discount, Retail.

I am trying to get in the Grand Total (which is the sum of the pivot) to show the 'real' sum of all the subtotals above. At the moment it is showing some kind of average (-$845 for Volume, but it should show the real sum of -$694.81).

Cheers,
Andy

 

[datiduke]

oh, how is Pdt calculated:

to get the Profit influence from volume P1: (PYprofit/PYQty)*(Qty-PYQty)
to get the profit influence from Cost & Discount: ((Profit/Qty)-(PYprofit/PYQty))*Qty*-1
to get the profit influence from Retail: ((Profit/Qty)-(PYprofit/PYQty))*Qty

Andy

 

[bendipa]

Subtotals of WHAT precisely.....? Which items for which year are you trying to subtotal? Give me the breakdown of what $-694 is supposed to include above? I'm not a mind reader! If you can't tell me then I can't help!

 

[datiduke]

Subtotals of WHAT precisely.....? Which items for which year are you trying to subtotal? Give me the breakdown of what $-694 is supposed to include above? I'm not a mind reader! If you can't tell me then I can't help!

All right, let me start over, as there was an error in the formula I showed before. In 2015 we made a profit (GP row) with our three products of +$2676 (P1 = $2609, P2, $112, P3 made even a loss of -$45). = $2676.
In 2016 the profit shrunk to $2468. A difference of $207.47. Of course everybody wants to know why it shrunk.

For us, there could be 4 reasons why this profit changed: Increase/Decrease in units sold (Volume), our stores have been allowing for more discounts, the vendor increased their price (our cost price) or we have/haven't adjusted our retail price accordingly to the cost increase/decrease. (you may have other factors like freight costs, but that's not included here.)

To determine, which of these 4 reasons had what influence to the overall profit change, I needed to break it down to unit values for each year (cost per unit sold, discount per unit sold, retail price per unit sold) and multiply them with the qty sold:

to get the Profit influence from a change in volume: (PYprofit/PYQty)*(Qty-PYQty) (do that with each product and add them together, you'll get -$694.81 (that is the reduction in profit due to a reduction in unit sales)
to get the profit influence from Cost: ((Cost/Qty)-(PYCost/PYQty))*Qty*-1 (do that with each product and add them together, you'll get -$518.06 (that is the reduction in profit due to an increase in the costs)
to get the profit influence from Discount: ((Discount/Qty)-(PYDiscount/PYQty))*Qty*-1 (do that with each product and add them together, you'll get +$249.31 (that is an increase in profit due to giving less discounts)
to get the profit influence from Retail: ((Retail/Qty)-(PYRetail/PYQty))*Qty (do that with each product and add them together, you'll get +$756.08 (that is an increase in profit due to an increase in the retail price > caused by an increase in supplier costs).

If you look at the small pivot picture, the lower one, you can see under the column VOLUME three SUBTOTALS for Product1 (-621.12), P2(-111.93), P3 (+38.24). If you ADD these SUBTOTALS together you get -694.81. I want this number to show in the GRAND TOTAL (calculated by PowerPivot), which currently shows -$845, but that is not correct!

Is that precise enough? I don't know what else you want? You may ignore the bigger pivot picture, it just shows the steps I have taken. (You find all the values in the vertical Pdt column in the horizontal view of the second pivot).

 

[Matt Allington]

I think only you can understand this But if your issue is "totals don't add up", then it is likely to be an issue with filter context. Read these posts (one by Rob, one by me) here and see if it sparks any ideas that can help you solve the problem.

Subtotals and Grand Totals That Add Up “Correctly” - PowerPivotPro

Nested SUMX or DAX Query? - PowerPivotPro

 

[datiduke]

Morning,
yeah, sometimes its pretty hard to write down weird thoughts

I have kind of figured it out yesterday, why it doesn't work. Well, not exactly for this here, but the reason is that my data is based on month, by product, by store. The formula is calculating on the lowest possible level. So if you had sales in Jan14 and no sales in Jan 15, the profit change would be distributed to volume. If the sale is done in Feb 15 instead, the sales would again be distributed to volume. If I know display the profit change on the yearly level, it would be the same quantity for that year (20 in 2014 and 20 in 2015), but the formula would probably still distribute everything on the monthly level.

Anyway, thanks for the posts, I'll read through it and wont give up finding a solution

Cheers and all of you a merry Christmas!
Andy

 

[bendipa]

Hm! Maybe I'm missing something here. But with "Cost: ((Cost/Qty)-(PYCost/PYQty))*Qty*-1", or "Discount: ((Discount/Qty)", you'd be dividing a figure by 0 as you have no data entered for Product2 2016. So as you have a blank cell for Qty that would be treated as division by 0 which would cause any calculation there to crash.

 

[datiduke]

Hm! Maybe I'm missing something here. But with "Cost: ((Cost/Qty)-(PYCost/PYQty))*Qty*-1", or "Discount: ((Discount/Qty)", you'd be dividing a figure by 0 as you have no data entered for Product2 2016. So as you have a blank cell for Qty that would be treated as division by 0 which would cause any calculation there to crash.

As there is not data for P2 in 2016, there is no Cost or discount either. So there was never a calculation for P2 in 2016, hence it is returning BLANK.

But you are right, it could turn into a problem if a store is cancelling the purchase (returns the quantity), but at a different cost, discount or retail price. But in this case you would use an ifferror or something.

Andy