0

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

Initial pivot table

From a source table like this:

Source data

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:

Adding one more value to the table

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

Desired result

(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.

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.

5
  • This sounds super promising, thank you for your help! I'm trying my best to follow your instructions, but both of those formulas are yielding the error "There is a problem with this formula," followed by Excel highlighting Billing[[#Headers]. Also, is there a special process to create a "derived column", or is it just adding an extra column in the source data table with that formula under the new header?
    – etudes
    Feb 20 '19 at 1:40
  • Excel will not like the formula until it has the correct table and column names. In your comment, the column name is missing. It may be easier to write the formula and select the fields from the table. See my edit to the answer.
    – Ted D.
    Feb 20 '19 at 6:29
  • The sample data table in the formula was named "Billing". The table name needs to match the one in the spreadsheet. If not changed, it is likely "Table1" or 2 or 3 ...
    – Ted D.
    Feb 20 '19 at 7:23
  • Edit the table name reference in the formula to match the one in the spreadsheet. The process to create a derived column is (1) add a new column to the table (perhaps with insert column) and (2) make it a derived column by using a formula (given in the answer) to "derive" the data rather than manually entering the data. So yes, just adding a column with the provided formula. And since this is strictly a "helper" column, if it is hidden, it won't matter (there is no data in it that is manually inputted or edited).
    – Ted D.
    Feb 20 '19 at 7:34
  • So brilliant, thank you for walking me through this. I understand it way better now. (you're right, it was called Table1). This leaves me with a slightly different solution than my initial intention — this splits up the Private Lesson hours between Private Lessons and Cancellations, as opposed to "Private Lessons" including canceled hours, with a separate column showing how many were cancellations (sort of doubling the data). But I've adapted to your solution by grouping Private Lessons and Cancellations, which creates a Subtotal of them.
    – etudes
    Feb 21 '19 at 4:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.