How can I add a summarized Average Rate row under my crosstab?

[psrpsrpsr]

I have a crosstab that displays an attrition rate for members canceling a service that looks like this:

| Location ID        | 201706 | 201707 | 201708 | 201709 | 201710 | 201711 | 201712 |
|--------------------|--------|--------|--------|--------|--------|--------|--------|
| 12345              | 1.8%   | 1.6%   | 1.5%   | 1.7%   | 1.5%   | 1.6%   | 1.5%   |
| 99876              | 1.9%   | 1.9%   | 2.2%   | 1.9%   | 1.9%   | 1.9%   | 1.7%   |
| Total Average Rate | 1.7%   | 1.8%   | 2.1%   | 2.0%   | 1.8%   | 1.9%   | 1.8%   |   <<< NEED TO ADD THIS

The Total Average Rate calculation for my dataset would be calculated as follows, using the dataset below:
201706: (96 + 218) / (6,000 + 11,964) = 1.7%
201707: (98 + 231) / (6,050 + 11,969) = 1.8%
...and so on.

How can I achieve this? THANKS!


| Location   ID | Year Month   Number | Cancel Count | Subscriber Count | Attrition Rate |
|---------------|---------------------|--------------|------------------|----------------|
| 12345         | 201706              | 96           | 6,000            | 1.6%           |
| 12345         | 201707              | 98           | 6,050            | 1.6%           |
| 12345         | 201708              | 108          | 6,100            | 1.8%           |
| 12345         | 201709              | 99           | 6,150            | 1.6%           |
| 12345         | 201710              | 104          | 6,200            | 1.7%           |
| 12345         | 201711              | 95           | 6,250            | 1.5%           |
| 12345         | 201712              | 109          | 6,300            | 1.7%           |
| 99876         | 201706              | 218          | 11,964           | 1.8%           |
| 99876         | 201707              | 231          | 11,969           | 1.9%           |
| 99876         | 201708              | 270          | 11,976           | 2.3%           |
| 99876         | 201709              | 267          | 12,012           | 2.2%           |
| 99876         | 201710              | 222          | 11,984           | 1.9%           |
| 99876         | 201711              | 251          | 11,978           | 2.1%           |
| 99876         | 201712              | 219          | 12,034           | 1.8%           |

[hespora]

Code Select Expand


total(
  [cancel count]
  for [year month number],[loc id]
)
/
total(
  [subscriber count]
  for [year month number],[loc id]
)

would be what goes into the rows, and then just

Code Select Expand


total(
  [cancel count]
  for [year month number]
)
/
total(
  [subscriber count]
  for [year month number]
)

goes below as a summary line. The trick is here, you do *not* add this as a summary, but at a second measure, putting the measures into rows - one below (logically) loc id, and one on the first level. Without having any data, the attached is how it should look like in RS.

[psrpsrpsr]

Hespora, you've come through for me again - you rock! However there was an unanticipated interaction with my crosstab - See below:

> My crosstab initially had the [Attrition Rate] nested underneath the [Year Month]. I also had two additional fields: [Attrition Rate - Last 12 mos.], and [ACH %]. These were displaying correctly with the pre-summary row configuration.

> With the attrition rate being nested after [Location ID] rows, the [Attrition Rate - Last 12 mos.], and [ACH %] are no longer displaying correctly. Is that due to the intersection of the two metrics?

> Is there a way to achieve the solution you provided in the last post, but with the [Attrition Rate - Last 12 mos.], and [ACH %] metrics displaying the same way they do in the top example?



Thank you again.

[hespora]

I gotta admit, I just never tried putting measures in *both* columns and rows. My gut feeling tells me the system is just gonna get confused as to what it's supposed to do at the intersection of two measures. Summaries could indeed do that, but not measures.

If you were to change the definition of attrition rate to "total ( [cancel count] ) / total ( [subscriber count] )" (leaving out the specification of for what level to aggregate), then put that as a default measure and add a summary, that *should* take care of both subtotals over location id and over yearmonth, but that still will leave ACH% unfixed.

Honestly, I do not know how to get what you want.

[psrpsrpsr]

Figured it out! I simply created a 2-column table, placed two crosstabs next to each other, and chose 'Box Type' = None on the right hand crosstab row headers and intersection cell. Thanks again for your help.