Search code examples
excelexcel-pivot

In excel pivotal table count unique values

An Excel table contains information when a component of a service is down. So service [column A] (MyService) has a number of components [column B] (component-1, component-2, component-3). Each row means that a component was down for say 5 minutes [time-of-day]. Other columns are used for filters, etc.

A service is considered down when any of the selected components (component-1, component-2) is down. So, the total unavailability time is considered 5 minutes, even when both component-1 and component-2 were down at the same moment. So the unavailability is NOT multiplied when more components are down for the service.

We would like to know how much time a service (as a series of selected components) was unavailable per day. We use a pivotal table. In the rows are the components. In the columns are the time-of-days. The rows are summarized to a day level.

enter image description here

The total unavailability time for the day should be 2 (and not 3) for June 12. So, that would be:

enter image description here

So, how to "summarize" the unavailability of the service per day without doubling unavailability of different components at the same moment?

Try-2: I tried working in the Pivotal table with 'max'. Then the "summary" of the column for a time-of-day is ok, but then the summary is the max of all columns for a day, i.e. 1. Alas.

Try-3: Essentially the value per day should be the number of different time-of-days! Or the number of different time-of-day columns per day. How can I show that number for a day?

enter image description here

Solution

  • If you cannot insert a Helper Column into your existing table, you can follow the steps below.

    If you are using Excel 2013 and above, when you first insert a pivot table, there should be an option to add data to data model which would be the last option in the dialog box as shown in the screenshot below. add to data model

    After that, just continue with pivot table insertion procedure as usual and build you pivot table as shown in your question.
    Now you can just select Distinct Count as Value Field Settings under the Values pane. now distinct count selectable

    I think you are using a version of Excel higher than or equal to 2013 (guessing from your screenshot).
    If your version is lower than that, e.g., 2010, you'll need to install PowerPivot Addin (downloadable from MS website).
    After installation, you just need to activate the addin via Options->Add-ins->Manage (COM Add-ins) -> Go -> tick PowerPivot and OK.
    Then import the data into PowerPivot and carry out the usual steps.
    It is quite straight forward to do it with PowerPivot (PowerPivot will insert the resultant Pivot Table back into the .xlsx with modified data intact) but if you require assistance, let me know and I will outline the steps with screenshots.