Consider a Pivot Table Value field column as a criteria for computing another Value Field column

{ 8 Comments }

Assume a simple three column dataset showing hours worked by different machine on different dates.  So column A is Date, column B is Machine Name and column C is hours worked.  There are duplicates appearing in column A and B .  Blanks in column C depict machine idle time.

The task is to create a simple three column dataset showing all unique Machine names in the first column, Last day on which the machine worked in the second column and hours worked on the last day in the third column.

This problem can be solved by using formulas (Refer first worksheet of the workbook) but if one has to use a Pivot Table, then there would be a few problems.

1. The Grand Total for the Date Field should be blank because on cannot determine the Last day on which the machine worked across different machine types.  A conventional Pivot Table shows the Maximum of all dates appearing in the Date Field.

2. The Grand Total for the Hours worked Field should be a summation of the total hours worked on last day across all machine types.  A conventional Pivot Table shows the Maximum of all hours worked appearing in the Hours worked Field.

3. The biggest problem of them all is that there is no way to give a criteria as the Last day for that machine for computing another Field in the Pivot Table.  Please refer the file for a better understanding.

This problem can be solved using the PowerPivot.  You may refer to my solution in this workbook.

Leave a Comment

Your email address will not be published.

*

  • Hi!

    may I suggest

    Last Day Worked=
    IF (
    ISFILTERED (‘Table1′[Equipment] );
    CALCULATE (
    MAX (‘Table1′[Date] );
    FILTER (
    ALLEXCEPT (‘Table1’; ‘Table1′[Equipment] );
    ‘Table1′[Hours Worked] > 0
    )
    )
    )

    and

    Total hours worked on last day=
    CALCULATE (
    SUM ( ‘Table1′[Hours Worked] );
    FILTER (‘Table1’; ‘Table1′[Date] = [Last Day Worked] )
    )

    • Hi,

      Thank you for your reply. While the result of ‘last day worked’ seems to work fine, I believe the result for ‘Total hours worked on last day’ is incorrect. The result as per your formula is 38,10 and 171. This is the total hours worked by each machine. We want to compute the hours worked on the ‘last day worked’ – the result should be 2,10 and 20.5