Calculate intermediate subtotals in a hierarchy and propagating in another calculation

[RichardRayJH]

Since I can't include attachments (new user restriction? It's sort of a pain...), here's a sample data set:

SeasonDate	LOBSuperSummary	LOBSummary	Net	Days
1/15/2013	Access	Ticket	$1,000.00	100
1/15/2013	Access	Pass	$800.00	50
1/15/2013	Training	Adult	$1,000.00	0
1/15/2013	Training	Junior	$900.00	0
1/15/2013	Training	Kids	$750.00	0
1/15/2013	Dining	Table Service	$500.00	0
1/15/2013	Dining	Quick Service	$500.00	0
1/15/2013	Dining	Bar Service	$500.00	0
1/16/2013	Access	Ticket	$1,000.00	152
1/16/2013	Access	Pass	$800.00	30
1/16/2013	Training	Adult	$1,000.00	0
1/16/2013	Training	Junior	$900.00	0
1/16/2013	Training	Kids	$750.00	0
1/16/2013	Dining	Table Service	$500.00	0
1/16/2013	Dining	Quick Service	$500.00	0
1/16/2013	Dining	Bar Service	$500.00	0

<tbody>
</tbody>

The hierarchy is


SeasonDate
LOBSuperSummary
LOBSummary


The measures are NetRevenue and Days


For each SeasonDate I need to calculate the subtotal of Days at the LOBSuperSummary level for a LOBSummary = a specific value ("Access", in this case). I want to carry it down into all lower rows in the hierarchy so that I can divide NetRevenue each LOBSuperSummary and LOBSummary by the subtotal of Days for SeasonDate.LOBSuperSummary.


I think I should be able to do that with some sort of a CALCULATE, something like:


=CALCULATE(SUM(RevenueStudy[Days]),RevenueStudy[LSS]="Access",RevenueStudy[SeasonDate] = ?????))

But I don't know how to specify the SeasonDate criteria, and even if I do it for only a single date I don't get the Days on all the rows.


How can I restrict it to a specific SeasonDate, and where's my error in the Days subtotal?

Here's a representation of the pivot table I want to get:

Row Labels	Sum of Net	Sum of Days	Access Days	Yield Calc I Want
1/15/2013	$5,950.00	150	150	$39.67
Access	$1,800.00	150	150	$12.00
Dining	$1,500.00	0	150​	$10.00
Training	$2,650.00	0	150	$17.67
1/16/2013	$5,950.00	182	182	$32.69
Access	$1,800.00	182	182	$9.89
Dining	$1,500.00	0	182	$8.24
Training	$2,650.00	0	182	$14.56
Grand Total	$11,900.00	332	332	$35.84

<tbody>
</tbody>

If the LOBSuperSummary member is expanded into the LOBSummary level the Yield calculation should be done with the LOBSummary level Sum of Days, unless it's 0, then the AccessDays total should be used. I think that's a simple IF statement in the yield formula?


The actual data runs to several million rows for 5 more hierarchy levels for 10 years of data.


(As context, I'm DBA and do data analysis for Jackson Hole Mountain Resort, a ski area in Wyoming, USA near the Grand Tetons.)

 

[MD610]

I think these 4 measures will get you your results:

[Sum of Net]:=SUM('Table1'[Net])
[Sum of Days]:=SUM('Table1'[Days])
[Access Days]:=CALCULATE([Sum of Days], 'Table1'[LOBSuperSummary] = "Access", ALLEXCEPT('Table1', 'Table1'[SeasonDate]))
[Your New Measure]:=IF([Sum of Days]=0, [Sum Of Net]/[Access Days], [Sum Of Net]/[Sum of Days])

Drop SeasonDate, LOBSuperSummary, and LOBSummary in the Row Labels of your Pivot in that order, then add the 4 measures above.

 

[RichardRayJH]

Yep, that does it. I needed the ALLEXCEPT concept, and the idea of adding the measures to the pivot table rather than on the PowerPivot sheet.

BTW, is the Add Measure... item accessible anyplace else other than by right clicking on the dataset name in the field list? Even knowing it exists I still have to click all over the interface until I track it down!

Thanks a bunch, that opens up a lot of new scope.

 

[MD610]

Yes, I think it's always easier to work on complex measures directly in the Pivot rather than trying to write it in the Measure Grid in the PowerPivot Window. That way you get an instant check on how your measures respond in various rows, columns, and subtotals.

You can also add measures directly from the ribbon on the PowerPivot tab. You must have a cell in a PowerPivot Pivot Table for it to be active.