
Add total only formula to Pivot? (No dimension level totals)
Stefan Wühl Sep 5, 2011 7:58 PM (in response to Marc Livingston)I think you could try this:
In presentation tab, set show partial sums only for the top dimension, and deselect for all other dimensions.
Then use as expression something like:
= if(rowno()=0,TOTALEXPRESSION, EXPRESSION)
where TOTALEXPRESSION is the one you want to use at the total level, and expression the one per dimension.
(ensure that both work as expected at the given location, for example take a look at the help regarding sum of rows with pivot tables).
Regards,
Stefan

Add total only formula to Pivot? (No dimension level totals)
Marc Livingston Sep 6, 2011 11:16 AM (in response to Stefan Wühl )show partial sums is grayed out on my chart.
I got it to work by adding a dimension 'Total', then told the expression that it can not equal any of the values of the next dimension, which suppresses it for all levels except the total.

Add total only formula to Pivot? (No dimension level totals)
Stefan Wühl Sep 6, 2011 11:22 AM (in response to Marc Livingston)That's sound interesting. But I don't get what you mean with telling the expression that it can not equal....
Could you post your expressions for the total dimension/expression or a sample app here?
Regards,
Stefan

Re: Add total only formula to Pivot? (No dimension level totals)
Marc Livingston Sep 6, 2011 11:31 AM (in response to Stefan Wühl )I added Dimension 1 as ='Total'
Dimension 2= Topincome ( values only of General and Administrative,Sales, Cost of Goods Sold, and Selling)
expression:
=if(Topincome<>'General and Administrative'
and Topincome<>'Sales'
and Topincome<>'Cost of Goods Sold'
and Topincome<>'Selling',$(Sales)$(COGS)$(NoInterest))
Since the only place where topincome='' or is null is on the total level of the dimension total, this is the only place the value shows up.
This is really only usable since I only have 4 values on the next dimension. If it were more, then it would not make since to use this solution.

Re: Add total only formula to Pivot? (No dimension level totals)
Stefan Wühl Sep 6, 2011 11:40 AM (in response to Marc Livingston)Ok, getting closer.
But then you have a dimension 'Total' / additional column in your chart, don't you?
And you are using a partial sum to show the total line, right?
Thanks for sharing,
Stefan
P.S: just saw your image, forget question 1)...

Re: Add total only formula to Pivot? (No dimension level totals)
Marc Livingston Sep 6, 2011 11:43 AM (in response to Stefan Wühl )I just have the chart set to indent mode, which adds the total columns to each dimension when they are expanded.

Re: Add total only formula to Pivot? (No dimension level totals)
Stefan Wühl Sep 6, 2011 12:03 PM (in response to Marc Livingston)Got it (and just learned about the indent mode)!
I think indent mode is also why the partial sums are grayed out.
So in the future, if the number of your dimension values increase or you want to get rid of the extra column,
I think you might wanna try again the above expression using rowno().
(This works also with indent mode, if you set partial sums with indent mode deselected, then switch to partial sums).
I've attache a sample of what I had in mind.
Anyway, thanks for sharing your solution, have a nice evening / day,
Stefan

TotalRowInPivot2.qvw 155.0 K

Add total only formula to Pivot? (No dimension level totals)
Ravi Achaliya Dec 13, 2011 6:47 AM (in response to Stefan Wühl )Hi,
I just read, understood and implemented what u hv shared above.
This is extraordinary feature.
Thanks a lot for sharing.
Keep it up.
Best Regards,
Happy Thoughts






