Calculated Item on Power Pivot

[thiagoortiz]

Dear all,

I'm a newbie about Power Pivot discovering this powerful tool today when I was searching a solution for my "big" problem...

I need to build a pivot table (Stock Equation template), basically is a time-table below:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[/TR]
[TR]
[TD]Consumption[/TD]
[TD][/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Stock[/TD]
[TD]10[/TD]
[TD]6[/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Days of Stock[/TD]
[TD]90[/TD]
[TD]60[/TD]
[TD]30[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

Where "Consumption", "Stock" are data of a table and "Days of Stock" is a formula: "Jan Stk" - "Feb Cons" - "Mar Cons" - "Apr Cons".

To complicate the exercise there are some row labels more as segment, product type, etc. that a user can add/remove of this pivot table.

So I need this formula becomes variant, considering the added/removed row labels.

I thinked to do this in VBA, but excel don't cross rows and columns in a calculated item of pivot table.

Someone could help me? Any suggestion how to solve it?

 

[scottsen]

So, generally you would just place the filters on "Slicers", and let the user just click the slicer and <poof>... it just works. I suspect writing the measure (calculated field) for Days of Stock might be interesting, but... in general, everything you are describing is pretty straight forward for power pivot.

If you don't have one, I suspect you will want a separate date table, btw. See Power Pivot Date Table | Power Pivot | Tiny Lizard