Forced Selection in Pivot Table

[marleygt]

Hi all!

Here I am again with another tricky question.

I would like to know if is possible in a list box, to have a column which is not considering Dimension limit or that I can force to make a total sum of some specific fields;

As is not easy to say in words, herewith you can find a sample which will be more clear to understand.

Inside you'll find a Pivot Table where my Dimensions are 'Company' and 'Month';

My Request is: to have total (by Month) for prev Year and Curr Year, plus, a colum where I can see total amount in case user select more then 1 month.

For instance: if I select

                                   Jan + Feb + Mar

                                   - 2014

                                   - Alpha

                                   I would like to see 38 (Jan 10 + Feb 15 + Mar 13)

Is there a possibility to do that in the same List box?

Thank you for your help or any suggestions!

Alex

[Gysbert_Wassenaar · Accepted solution]

Do you mean like this?

[dmac1971]

Alex there is always a solution, could you show us exactly what you want in excel, even if you just mock up the table?

You could of course use containers on the sheet, allowing multiple different views in the same page area?

I've added another straight table that might do what you are looking for.  The headings are dynamic and change depending on the month selection, however when lots of months are selected it changes from showing the actual months selected to showing those not actually selected.  There may be a way around this I'm not sure.

Keep at it nearly there

[marleygt]

Ok! With your last (both) reply I think I should be able to solve!

I miss just one thing that I am sure you know how to handle easily.

Using gwassenaar‌ solution, is working right for my purpose but, need to understand how to show right data here:

If I select Jan and one Company:

Then Feb and same Company:

Jan+Feb Selection:

So, Total is ok! GREAT! 😄

New Problem:

and even If I take several selection:

It gives me absolute total of all Month (or selected Months) and equal result for all Companies. That's is not good.

Is possible to have right result in this case?

If there is a way also for this, I think goal will be reached completely!

Alex

PS: dmac1971‌ if you still need the excel - I can prepare and upload, but solution is closer...

[Gysbert_Wassenaar]

That looks like you're using sum(total Something) instead of sum(total <Company> Something) or perhaps in your case sum(total <Azienda> Something). Replace the Something with whatever field you want to sum.

[dmac1971]

How many years data do you have in your model?  If you have more than 2014 and 2015 I suspect the 603 total is coming from previous years as well?

[marleygt]

This is the Expr;

I've just added "Total <DimensioneCategory>" as you suggested - which is a multiple field (Company,Product,Type,Brand...etc);

If I select for instance Brand,

then data will be:

I mean, I can't use just one Single Field...but should it work even with multiple fields I suppose.

What I am doing wrong?

[marleygt]

4 Years is calendar capability, but data are filled up with 3 at the moment.

[dmac1971]

So where it says Valore above, translated to Value I think, what did you expect to see here instead of 42,976?  Total for the four years for this account, or for 2 years etc etc.  As Gysbert stated you are adding up everyone for everything I think?

[marleygt]

Perfect solution might be:

No Selection (total of the year - in this case, selected of course 2014 or 2015..etc)

(In this case without selecting a Company name - I've selected for show right result I wish to see without selection)

When I select - let say - Jan, 2014, then the same data in corresponding year slot

(Firs column in this case will be the same of Volume 2014 - That's right)

And....finally here we are at the real reason of this post:

If I select Jan + Feb, I want to see sum of both month (771 - result of Jan 395 and Feb 375 [with decimals not displayed]).

So Need to see without no selection (exception made for the year as I said before) , total of the year, but not the Grand Total of all years and much more important, not the same result for all the different Companies (that makes non-sense), but right result for all companies.

As words are always more complicated, here:

Alex

[marleygt]

dmac1971‌ gwassenaar‌

Morning Guys!

Just to let you know that with few further checks, everything is now working perfectly!

Thank very much for sharing your time, ideas and suggestions!

Have a nice day!

Alex