Measures not selecting correct data, then Pivot Table displaying Sub Totals that ignore filter RRS feed

  • Question

  • I'm going to try to be descriptive and describe this issue in order, but I know I'm going to leave something out no matter.

    This is the input file.  There is an Out Date (associated with a Calendar table), a booking status and Total Rent.  In this screen shot, the July Events with Contracted Status have sales equal to $112,852.12.  


    I added a Calendar in Power Pivot and connected the Calendar to the input table on Out Date.  I added Date Hierarchy to the Rows and created Measures to calculate Total Sales Contracted and Total Sales Definite.

    First, when I show the data with everything collapsed, you can immediately see the Subtotal for July Contracted is incorrect.  It has added the $43,700 (Definite July Sales) to the 112,852.12 to get $160,152.12.  Also, it has the same total for Total Sales Definite.  Total Sales Definite should have a total of $43,700 with a date of 7/29/2020.

    If I expand July, the Contracted shows the correct detail underneath (if you add the values it equals $112,852.12), but Definite does not show the correct detail.  It shows $19,950 (Contracted for July 12, 2020) instead of $47,300.00.

    I read that the Subtotals are ignoring the Filter that takes place in the measure.  How do you get around that?  And, why is the Definite Measure showing both the wrong detail line and the wrong subtotal?

    Thanks for reading this far and any help you can offer.  

    Wednesday, February 19, 2020 3:35 PM


  • I think I found the answer.  I have to make Calculated Columns in Power Pivot that  basically do the filtering so the Pivot Table doesn't have to deal with the FILTER being in the measure.

    Basically, I added a column for each booking status:

    =IF(SEARCH("Contracted",[Booking Status],1,0),Sheet1[Total Rent],0)

    Then, just add the Columns from the table instead of using the measures.  If I had a large table this could be problematic, but I'm dealing with very small amounts of data so I can make it work this way.  If anyone has a workaround for the issues mentioned in the first post, please share it because I think it's more correct to use measures and I'm always interested in learning.  But, don't spend a lot of time on this because I have found a way to get what I need.

    One bad thing I've discovered heading down this rabbit hole, is that if you need to add columns to the pivot table that are external to the original table, the only way to get it to continue to be able to fit into this approach is to add a calculated column to the original table for each external value you want to add.

    Wednesday, February 19, 2020 4:24 PM