# Issue with Partial Sums

**Marcelo Lapertosa**Jul 11, 2017 9:36 AM

I have an invoicing report that comprehends the years of 2016 and 2017, and my goal is to check the average price change I had from 2016 to 2017.

I'm using the following Dimensions in a Pivot Table:

Division

Customer

Item

The first expression I used is to sum only how many items were sold that only appear in both years, so it is as follows:

**Qty 2016:**

if( sum ( {<Year={'2017'}>} Qty ) >0 and sum( {<Year={'2016'}>} Qty ) >0 , sum( {<Year={'2016'}>} Qty ))

**Qty 2017:**

if( sum ( {<Year={'2017'}>} Qty ) >0 and sum( {<Year={'2016'}>} Qty ) >0 , sum( {<Year={'2017'}>} Qty ))

Which worked perfectly, and I did the same to find how much was the average unit price that I sold for, which is the following:

**Avg. Price 2016:**

if( sum ({<Year={'2017'}>} Qty ) >0 and sum( {<Year={'2016'}>} Qty ) >0 , sum( {<Year={'2016'}>} Total Invoiced )) / if( sum ({<Year={'2017'}>} Qty ) >0 and sum( {<Year={'2016'}>} Qty )>0 ,sum( {<Year={'2016'}>} Qty))

**Avg. Price 2017:**

if( sum ({<Year={'2017'}>} Qty ) >0 and sum( {<Year={'2016'}>} Qty ) >0 , sum( {<Year={'2017'}>} Total Invoiced )) / if( sum ({<Year={'2017'}>} Qty ) >0 and sum( {<Year={'2016'}>} Qty )>0 ,sum( {<Year={'2017'}>} Qty))

And finally to compare both years I used three expressions, one to find the total invoiced on 2017, another to multiply the qty sold 2017 by the average price of 2016 and finally one to check the percetage that increased from one to the other:

**Total 2017: **

if( sum ({<Year={'2017'}>} Qty ) >0 and sum( {<Year={'2016'}>} Qty ) >0 ,sum( {<Year={'2017'}>} Total Invoiced ))

**Compare 2016:**

(if( sum ({<Year={'2017'}>} Qty ) >0 and sum( {<Year={'2016'}>}Qty) >0 , sum( {<Year={'2016'}>} Total Invoiced )) / if( sum({<Year={'2017'}>} Qty ) >0 and sum( {<Year={'2016'}>} Qty ) >0 , sum( {<Year={'2016'}>} Qty ))) * (if( sum( {<Year={'2017'}>} Qty ) >0 and sum( {<Year={'2016'}>} Qty ) >0 , sum( {<Year={'2017'}>} Qty )))

**% Price Change:**

((if (sum ({<Year={'2017'}>} Qty ) >0 and sum( {<Year={'2016'}>} Qty ) >0 , sum( {<Year={'2017'}>} Total Invoiced ))) / ((if( sum( {<Year={'2017'}>} Qty ) >0 and sum( {<Year={'2016'}>} Qty ) >0 , sum( {<Year={'2016'}>} Total Invoiced )) / if( sum( {<Year={'2017'}>} Qty ) >0 and sum( {<Year={'2016'}>} Qty ) >0 , sum( {<Year={'2016'}>} Qty ))) * (if( sum( {<Year={'2017'}>} Qty ) >0 and sum( {<Year={'2016'}>} Qty ) >0 , sum( {<Year={'2017'}>} Qty ))))) -1

The final report is something like this:

The issue is when I go to the Chart Properties under the Presentations tab and select to Show Partial Sums to the DIVISION dimension, for example, it is not showing a straigth sum of the columns, but is actually showing a number that has nothing to do with anything. I only found that out after migrating the chart to Excel.

Any idea of how can I fix this? (Sorry for the long expressions, if you have a solutions to shorten it I'll appreciate a lot as well!)