Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello. I am wondering if there is a way to create a separate totals row in addition to the grand totals row in a pivot table. For example, if I have the table below, here is what I am trying to do:
M&M's
Red 6
Green 4
Blue 2
Yellow 5
===========
Red and Blue subtotal 8
Grand Total 17
How do I create the conditional subtotal? Thank you
You probably need to tick MultiLine Setting> adjust Wrap Cell Height to show the next line created by chr(13)
Hi Robert,
Try and make use of Dimensionality() function.
Example in expression :
=If(Dimensionality() = 0
,Sum({<Colour = {'Red','Blue'}>}[M&M]) & chr(13) & Sum([M&M])
,Sum([M&M])
)
should look like below:
Label for Totals:
=If(Dimensionality() = 0
,'Red and Blue subtotal' & chr(13)& 'Grand Total'
)
What if the values are calculated? For example, if the value for blue M&M's is actually created using a formula and not a straight value from the table?
hmmm, I suppose the logic should be Red & Blue = exressionRed + expressionBlue.
Can you post a sample data/app?
Jonathan I have tried your suggestions (modifying my data for security purposes and creating a view of straight data) and so far have a table that looks like this:
I used this code to create the table above:
=If(Dimensionality() = 0,Sum({<grp_typ = {'1. GROUP A','2. GROUP B','3. GROUP C'}>}[Current Month]) & chr(13) & Sum([Current Month]),Sum([Current Month]))
and this for the label:
=If(Dimensionality() = 0,'Missing Total' & chr(13)& 'Grand Total')
what am I doing wrong? I am trying to make my table look like this:
You probably need to tick MultiLine Setting> adjust Wrap Cell Height to show the next line created by chr(13)
OK Jonathan I am much closer now. Here's my table:
I am trying to figure out why my percentage totals are so far off now. Thank you for your help