Pivot table query

[chc007]

Hi there,

Output of my pivot table adds up all the below months sales but I would like to add function on a table where I could deduct sales of Oct'11 to Dec'11 and it only gives sales of Jan'12 and the same way for other months which will only give recent months sales figure...

is it really possible?


Month Client Currency sales
Jan'12 x EUR 18725
Dec'11 x EUR 7664
Nov'11 x EUR 7510
Oct'11 x EUR 6515

 

[powerpivotpro]

Can you provide an example of what the pivot looks like today and then an example of what you want the "new" pivot to look like?

And are you using PowerPivot?

 

[David Churchward]

Hi

I might be misinterpreting your question, but this seems like a simple CALCULATE. You can get guidance on this from the links below:

http://www.powerpivotpro.com/2009/12/daxs-calculate-function-pivots-will-never-be-the-same/

http://www.powerpivotpro.com/2009/12/dax-calculate-and-rapid-iteration/

http://www.powerpivotpro.com/2009/12/powerpivot-dax-calculate-is-a-supercharged-sumif/

Either that or you simply need to set Month as a report filter, row heading, column heading or slicer.

Thanks
David

 

[chc007]

Hi Rob and David,

Many thanks for your quick reply.

Yes report filter on month is solving my initial issue....

You guys sounds expert...and I need to update my skills in Pivot.

I will go through your links and improve myself.

Thank you, again.

 

[chc007]

I am currently using Excel 2010....

By the way what is power pivot... is there any videos to learn more on that?

 

[David Churchward]

Rob's done a great job of explaining it on the link below and the site contains everything you need to know on how to use it. I learnt most of PowerPivot from Rob and his blog. It's awesome!

http://www.powerpivotpro.com/what-is-powerpivot/

 

[chc007]

Hi there,

I have pivot table based report and it is something that I can filter on month basis.

If I select one month, I can see that particular month which appears.

But if I select more than one month, it appears as multiple.

Is there any way where instead of multiple, it could show all months or atleast 2 to 3 months whenever I filter it can show Jan. Feb. Mar. ?

Please let me know.

Thank you.

 

[David Churchward]

I'm finding it a bit tricky to visualise your problem.

Do you have Month / Date as a column on your pivot? You can set the month or date as a column and also set it as a slicer thereby giving you the means to filter your report (using the slicer as opposed to report filter) and display individual periods on your report.

Hope this helps

 

[chc007]

Thank you.

It is month as a column.

Slicer would give individual month data.

And I would like to have 2 or 3 months data where it would be summary of 2 to 3 months data doing addition... subtraction...and other calculations..


I would say, it is not possible my way.

Many thanks.

 

[David Churchward]

I might be reading your query incorrectly, but you can select multiple slicer items. Hold the Ctrl button as you select.

If you're conducting aggregations within a month (but possibly including data from other months), you can also do that. I've done something similar with moving averages in this post:

http://www.powerpivotpro.com/2011/11/trended-moving-averages/