Home › Forums › Power Pivot › Pivot table based on a pivot table?
Tagged: PowerPivot
This topic contains 1 reply, has 2 voices, and was last updated by tomallan 5 years, 6 months ago.
-
AuthorPosts
-
September 10, 2018 at 4:46 pm #10555
Hi, is it possible to create a pivot table based on the results of another pivot table.
I have an inventory table that has items and a measure that calculates slow movers and fast movers based on how many units are sold each week.
I then pull the information from the powerpivot table into a pivottable currently and copy the results to a new table. Then I filter the results to SLOW or FAST.
I cant put a slicer in as the results are based on a measure. And you cant run slicers based on measures.
Any thoughts on how to do this best.
Below is my measures. I first calculate how long the units have been on the floor based on when they were first TRANSFERRED not based on first RECEIVED. Then I consider units sold over the whole style not SKU. and if we have sold less than 6% per week. It’s a slow mover.
FIRST SENT TO STORES AGED UNFILTERED:=CALCULATE(MAX(‘TRANSFERS'[FIRST SENT TO STORES]),FILTER(ALL(dCalendar[DATES]),’dCalendar'[DATES]<=MAX(‘dCalendar'[DATES])))
SELL THROUGH TREND:=IF(‘tblInvSkuMaster'[FIRST SENT TO STORES AGED UNFILTERED]*.06<[SELL THROUGH],”FAST”,IF(‘tblInvSkuMaster'[FIRST SENT TO STORES AGED UNFILTERED]*.05>[SELL THROUGH],”SLOW”))
September 10, 2018 at 7:53 pm #10557Steve,
For the first measure, other than product “style” and a calendar period, what does your first pivot have on rows, columns, report filter, and slicers?
For Power Pivot, one of the first things I try in a case similar to yours, is to create a “custom” or dynamic table that would use a measure like [FIRST SENT TO STORES AGED UNFILTERED] is an added column (ADDCOLUMNS) and then use the dynamic table as an input for a measure like [SELL THROUGH TREND].
If you could produce some sample data (fictitious data that uses a real model), I might be able to be more specific.
-
AuthorPosts
The forum ‘Power Pivot’ is closed to new topics and replies.