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]])
Special to a the name for the "
No Show OR
Late Cancel" category.
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
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.