Price is calculated as (Current Period Avg Selling Price - Prior Period Avg Selling Price) * Current Period Volume.
This as well as other calculations (volume, inflation) have to be calculated at the individual SKU level. The calculations of these values is being done without issue but I would like to be able to use sums of these calculations to show trending (such as Price as a % of Revenue trending over time for the entire business unit).
I think I'd have to pass the values at the SKU level into a table and re-consume that table with Tableau. Can anyone point me to instructions for how to do this (take the correct sum of one of these calculated fields, like Price, so it can be plotted on a chart for instance)
Thanks
I can point you in a direction - the basis for your CY and PY calculations are done at the record level - a GL date is either in this year or it is in last year - so you for each Item you need to total all the sales in the Current Year and divided by the count associated with the sales for the current year to return the current year average - then do the same for the prior year - but you need to use LODs to total each numerator and each denominator separately - then do the math
on the red tab, I just did an example to calculate the current year as an example - I'm not a finance guy and not certain of the values you wanted to use so I just used sales/quantity shipped - you can change as needed
Thanks
Jim - Tableau Visionary
