Powerpivot or regular pivot table

[ajw5173]

Hi,

I am creating a report that requires the ability to show departments and expand to show the vendors in the department. I believe that a pivot table or powerpivot is the only way to do this as the data is variable. I have a column of sales and TY/LY in the row along with the SKU information. I have Department as a row lable year as a column label and Sum of sales in the values section. Is there any way to get a variance of theses two columns or do I need to change the way my data is constructed?

Any help would be GREATLY appreciated!

Thanks!

 

[theBardd]

If I get this correctly, either pivots should work fine, although Power Pivot gives you more flexibility if you want to take it further.

In a pivot table, all you need to do is create a calculated field (Pivottable Tools>Options>Calculations, Fields, Items & Sets>Calculated Field) with a variance formula something like

=(Sales-'TY/LY') /'TY/LY'

 

[ajw5173]

Can I use that same formula in a power pivot pivot table between 2 related tables?

 

[theBardd]

A Power Pivot measure would be a different syntax/ If you supply the layout of the two tables, it should be simple to give you the DAX formula.