Excel: non-nested data column in Pivot Tables

Question

I've created a Pivot Table that looks like this:

From a source table like this:

I am trying to add one more value to the table, "Cancellations" (which are already included in the "Private Lessons" total). I created a set to add the "No Show" and "Late Canceled" columns together for that value. This is what happens when I add that into the Pivot Table:

The main thing I'm trying to achieve is this:

(Or perhaps someone knows of a more elegant way of presenting this data). But I just can't seem to separate the Cancellations value into its own column, it seems any new added field must be nested, no matter what Pivot Table area you put it in.

I need to be able to use the "Show Report Filter Pages" feature to generate many pivot tables across sheets, otherwise I would just create a second pivot table side by side.

A side goal is for the cancellations to be calculated as a sum of Hours, rather than number of cancellations.

Is there a way to achieve this? I attempted many Google searches, but I wasn't quite sure how to word this problem and could not find a solution.

Answer 1

Edited: added manually entering the formula by selecting cells (see below).

Add a derived Column to your table. Lets call it Service Category Derived. Enter this formula into the first data cell of this new column:

=IF([@[No Show]]<>0,Billing[[#Headers],[No Show]],IF([@[Late Canceled]]<>0,Billing[[#Headers],[Late Canceled]],[@[service category]]))

changing the column names and table name as needed.

If you want the No Show and Late Cancel to be a single category, then use:

=IF(OR([@[No Show]]<>0,[@[Late Canceled]]<>0,Billing[[#Headers],[Late Canceled]]),"Special",[@[service category]])

changing Special to a the name for the "No Show OR Late Cancel" category.

Do a Data tab, Queries & Connections section, Refresh All. Then edit the pivot table chosen fields, deselecting the Service Category field and selecting the Service Category Derived field. Drag the Service Category Derived field from wherever it appears back to the Columns pane of the PivotTable.

The hours will also be fixed with this adjustment.

Edit: How to manually enter the formula using cell selection.

Select the first data cell of the new column. Type =if( select the cell in the same row (first data row) of the No Show column. Type a <>0, then select the No Show column header. Type ,if( select the first data item of the Late Cancel column. Then type <>0, then select the Late Cancel column header. Then type , followed by selecting the first data cell of the original service category column and end with )) All the selected cells are from the same row except the header cells. Excel may have already provided one closing parenthesis so make sure there are two total (one to close each IF).

The single combined category formula is similar. Again, type the following into the new column's first data cell, clicking on the cell being described: =if(or(select first data cell of "No Show" column, type <>0,select first data cell of "Late Canceled" column, type <>0),"type name for the "No Show/Late Cancel" category (between double quotes, as shown in the previous and following, highlighted text), type ",select first data cell of original "service category", type )
Be sure to type all the highlighted characters including " , ) .

The "No Show" and "Late Canceled" columns appear to be used like boolean and not a Count or amount so making them Boolean would make the formulas easier to read since there would be no need to compare to zero <>0 that text could be deleted =IF([@[No Show]],Billing[[#Headers],[No Show]],... (which reads: If this row's data in the "No Show" column is true then use the table header of the "No Show" column). Further, since false is the default for boolean, empty cells would be considered false (e.g. not a no show). So only TRUE need appear in those cases where it is a "No Show" and leave all the false cells blank making the data easier to visually digest.