Customize Sub-Totals in a Pivot Chart

[#1]

I have a pivot chart that has several dimension (year/month/region/state) and two measures (Sales/Units).  The measures are set to a toggle where when SALES is selected from the toggle filter - only the sales figures show - pivoted by month.  Similarly, when Units is selected in a toggle filter, only the UNITS display - pivoted by month.

The challenge is  to define the sub-total calculation.  When Sales is displayed - the subtotal formula should be set to SUM.  When UNITS are displayed, the sub-total formula should be set to AVERAGE.

Any help would be appreciated.

Thanks.

Tom

[JonnyPoole]

Hi Tom !

Are you using a data model field or a variable to store the 'toggle' ?

I think you just need an expression like this:

if (   <..toggle...> = 'Sales' ,  sum(Sales),  if (   <..toggle...> = 'Units'  ,sum(Units) ) )

but if you are doing other things in the expression that require a more fine tuned example can you post the current expression or the qvw ?

cheers,

Jonny Poole

[#3]

I am using a Set Analysis statement to sum the Sales figures…..otherwise your logic is similar to what I have

Thomas Berard | PMO Consultant | Global Technology, RAD-Americas Planning & Effectiveness |<https://team.amer.mymetlife.com/Teams/bmosecure/default.aspx>

Tel: 401-827-2867 | Cell: 401-556-0239 | Email: tberard@metlife.com<mailto:tberard@metlife.com>

To submit Service Requests click here for Assist<https://team.amer.mymetlife.com/Teams/IBITPMO/Web%20Part%20Page%20Library/SN%20in%20Connect.aspx?PageView=Shared&InitialTabId=Ribbon.WebPartPage&VisibilityContext=WSSWebPartPage>

[JonnyPoole]

ok the approach  SHOULD still work. IE:   if the toggle has sales selected then the entire expression below will be used including any SET ANALYSIS statements in the sum( )  expression for sales .

if (   <..toggle...> = 'Sales' ,  sum( {SETSTATEMENT} Sales),  if (   <..toggle...> = 'Units'  ,sum(Units) ) )

do you see an issue ?

[#5]

That is the logic I have and it works. My issue is that the user wants to see a sub-total calculation but that the formula varies dependent on the toggle. When it is set to Sales – it should be a SUM; when it is set to Units – it should calculate the AVG.

Thomas Berard | PMO Consultant | Global Technology, RAD-Americas Planning & Effectiveness |<https://team.amer.mymetlife.com/Teams/bmosecure/default.aspx>

Tel: 401-827-2867 | Cell: 401-556-0239 | Email: tberard@metlife.com<mailto:tberard@metlife.com>

To submit Service Requests click here for Assist<https://team.amer.mymetlife.com/Teams/IBITPMO/Web%20Part%20Page%20Library/SN%20in%20Connect.aspx?PageView=Shared&InitialTabId=Ribbon.WebPartPage&VisibilityContext=WSSWebPartPage>

[jvitantonio]

Hi,

Take a look at the attached app.

JV

[JonnyPoole]

got it... missed the need for average in the post. my oversight. good solution below.

[#8]

Closer….

The total number for the UNITS for the month should be the sum or all the units for the Month. However, the Sub-Total in the pivot should be the AVG.

The solution you had assumed that there was an average of all of the detail records. I added a line to your inline data:

This is my result.

I would want to see a Subtotal AVG of 13.

Thanks.

Thomas Berard | PMO Consultant | Global Technology, RAD-Americas Planning & Effectiveness |<https://team.amer.mymetlife.com/Teams/bmosecure/default.aspx>

Tel: 401-827-2867 | Cell: 401-556-0239 | Email: tberard@metlife.com<mailto:tberard@metlife.com>

To submit Service Requests click here for Assist<https://team.amer.mymetlife.com/Teams/IBITPMO/Web%20Part%20Page%20Library/SN%20in%20Connect.aspx?PageView=Shared&InitialTabId=Ribbon.WebPartPage&VisibilityContext=WSSWebPartPage>

[tresesco]

Use SecondaryDimensionality() like in attached qvw.

[#10]

i dont see this working as planned