Pivot Table Sub totals

[lfholland]

I have a pivot table I have created.  The requirement is to have Net Sales/Margin down the side and broken up by months across the top with YTD last year, YTD this year and Variance at the end.  No problems with that.  This issue is getting a subtotal.  It will only display the subtotal for the Net Margin % and not both Net Sales and Margin %.

My dimension for Net Sales/Margin % is the following:

=ValueList('Net Sales','Net Margin %')

I have three other dimensions along the left-hand side that are just the dimensions selected, not calculated dimensions.

My expression for one of the months is the following:

If(ValueList('Net Sales','Net Margin %')='Net Sales',

Num(sum({$<Year={"<=$(=Max(Year))"},MonthName={">=$(=addmonths(today(), -8)) <=$(=addmonths(today(), -7))"},Month=>}NetSales ),'$###,###,##0'),

Num((sum({$<Year={"<=$(=Max(Year))"},MonthName={">=$(=addmonths(today(), -8)) <=$(=addmonths(today(), -7))"},Month=>}NetMargin )
/
sum({$<Year={"<=$(=Max(Year))"},MonthName={">=$(=addmonths(today(), -8)) <=$(=addmonths(today(), -7))"},Month=>}NetSales )),'###.#0%')
)

Below is a screen shot of what it is doing.   Should I change the design to get both subtotals, if so how?  Or is there another solution?  Any help is appreciated.  Thank you!

[Zhandos_Shotan · Accepted solution]

Hi!

Try this:

1) Add to PlanGroup dimension real 'Total' text value.

2) Use additional condition for measures. First condition is for 'Total' dimension value with TOTAL<PlanGroup > addition, and orignal measure expression for another values:

IF(PlanGroup='Total', 

If(ValueList('Net Sales','Net Margin %')='Net Sales',

Num(sum({$<Year={"<=$(=Max(Year))"},MonthName={">=$(=addmonths(today(), -8)) <=$(=addmonths(today(), -7))"},Month=>} TOTAL<PlanGroup > NetSales ),'$###,###,##0'),

Num((sum({$<Year={"<=$(=Max(Year))"},MonthName={">=$(=addmonths(today(), -8)) <=$(=addmonths(today(), -7))"},Month=>}TOTAL<PlanGroup > NetMargin )
/
sum({$<Year={"<=$(=Max(Year))"},MonthName={">=$(=addmonths(today(), -8)) <=$(=addmonths(today(), -7))"},Month=>} TOTAL<PlanGroup > NetSales )),'###.#0%')
)

,  //ELSE

If(ValueList('Net Sales','Net Margin %')='Net Sales',

Num(sum({$<Year={"<=$(=Max(Year))"},MonthName={">=$(=addmonths(today(), -8)) <=$(=addmonths(today(), -7))"},Month=>}NetSales ),'$###,###,##0'),

Num((sum({$<Year={"<=$(=Max(Year))"},MonthName={">=$(=addmonths(today(), -8)) <=$(=addmonths(today(), -7))"},Month=>}NetMargin )
/
sum({$<Year={"<=$(=Max(Year))"},MonthName={">=$(=addmonths(today(), -8)) <=$(=addmonths(today(), -7))"},Month=>}NetSales )),'###.#0%')
)

)

Also for better performance and readabilty, you can replace valuelist with real island table dimension

Load * inline

[Measures

'Net Sales'

'Net Margin %'];

and replace If(ValueList('Net Sales','Net Margin %')='Net Sales' , expr1, expr2)

with 

Pick(Match(Measures, 'Net Sales', 'Net Margin %'), expr1, expr2)

[lfholland]

How do I "1) Add to PlanGroup dimension real 'Total' text value." ? Once I figure that out, I think this solution will work! 🙂

[Zhandos_Shotan]

Adding dummy row to table with PlanGroup dimension at script. Something like:

Concatenate load

'Total' as PlanGroup,

0 as Fieldname1

0 as Fieldname2

...

its depends on your data model.

OR,

you can preagregate values and join Total row to table:  ..Sum(NetSales) ... group by PlanGroup..

But exclude Total row with measures when its unnesesary: <PlanGroup -={'Total'}>

[lfholland]

Thank you!