RichardRayJH
New Member
- Joined
- Jan 18, 2013
- Messages
- 24
Since I can't include attachments (new user restriction? It's sort of a pain...), here's a sample data set:
<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:
<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.)
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.)