Total Mode 'greyed out' - no partial sums showing

[ljackson]

I am trying to show partial sums on my pivot table, I normally have no problems, so can't understand what why today I'm having difficulties!

I have selected the 'partial sum' on the relevant expressions in the presentation tab, but no total rows appear.

I noticed in the expressions tab the Totals Mode box is completely greyed out ....for all the expressions (see attached screen shot).

Can anyone point in the direction of enabling the totals?

Many thanks

.

[Clever_Anjos]

Tick "Show Partial Sums" to each dimension you want

[ljackson]

Partial sums is ticked on every dimension already.

Does anyone have any other suggestion?

Thanks

.

[Colin-Albert]

Is Indent Mode ticked on the Style tab, this can change functionality with Pivot table properties.

[Clever_Anjos]

Maybe your expression is not returning subtotals.

Please add a fixed expression like 1 and check if it shows

[ljackson]

I gave that a try Colin, and hoped for a miracle ....it didn't happen!  

I ticked 'indent mode' and then the chart turned into a hideous mess.   I can see what it was trying to do, but wasn't what I needed for this.

I've not used that funtionality before, but now I know what it does.

[ljackson]

I added the new expression, as you suggested, and the chart listed all products (regardless of the 40% margin restriction), along with the totals at the bottom of each product group.

I guess the way I've manipulated the expressions to filter out the lower performing products is affecting the calculation process of the total rows.

I now know that the partial sums are working, they are just not compatible with my expressions.

Thanks for your help.

[Anonymous]

Leah,

You need to check "Show Partial Sums" for each Dimension.  Each level that you check shows the aggregate subtotal for that dimension.  Right now you have partial sums checked for "Prod Grp No" only.  Since that is the highest level, at the moment, the pivot chart will only display a grand total of everything.  If you want subtotals for each "Prod Grp No", you need to select the dimension that is one level below, which is "Prod Grp Desc".

I understand how the Presentation tab is confusing.  If you notice, there is a border around the top section labeled "Dimensions and Expressions."  Everything within that border applies only to the currently selected dimension or expression.  Everything in the below section, which is unlabeled, applies to the entire chart.

I hope this helps.

[Clever_Anjos · Accepted solution]

Try to AGGR your expression

SUM(

AGGR(

     copypasteyourexpressionhere,

     Dim1,Dim2,Dim3  // All of your dimentions

)

)

[#10]

Hi Leah,

I am facing the same problem as yours. Is your problem solved? To resolve this problem I changed my expression using sum(Aggr(Expression),Dim1,Dim2,Dim3.....)) and I achieved my Output, But the chart becomes very heavy as loading time increased a lot because I am using 15 Dimensions and 44 Expressions. Do you have any solution for such kind of problem? Please suggest me any solution to come out of it.

Thanks.

Regards,

Yojas

[#11]

Hi

Instead Try to aggregate it in the scripting rather than IN the chart

Regards