locked
PowerPivot - Drill Down Data Incorrect when using multiple selections (in the filters) RRS feed

  • Question

  • Okay, I need some help here, I don't if I'm not defining the question correctly or if there are no answers.

    Problem

    When I click on a value within the pivot table, say 30 units in 2017, Jan of which I pick medium and small units from a filter above. I expect to see those 30 units/rows, but I get 131 rows returned and seem to of ignored the year. 

    Can anyone help?

    Background

    Units - Values under columns
    Month - in the Rows
    Year in the filter
    Unit Size in the filter and Multiples selected

    I have a pivot table with a data model attached (ThisWorkbookDataModel) and an SQL connection in the same workbook. This is being used with PowerPivot to generate a pivot table. This table is large about 60 megs and I'm trying to keep the size down. Also, i have a really awkward user base and it's gotta be simple for them to use.

    OS - Windows 10

    Excel version - 1705 (Build 8201.2209)

    Thursday, December 28, 2017 9:59 AM

Answers

  • Okay, for anyone reading this thread.

    Firstly support here isn't very good, lots of experts and no real answers.

    I trawled the web to find a solution for my problem and there appears that PowerPivot behaves differently when it comes to handling dates.
    If you split the date into year and month you have to create a hierarchy with PowerPivot for it to out the correct data.
    But and there is always a but, the PowerPivot table can no longer separate these two fields. So the year can't be kept in the filters whilst the months are in the row headers.

    Make sense why PowerPivot hasn't taken off.

    • Marked as answer by Cheap Noodles Wednesday, January 17, 2018 1:53 PM
    Wednesday, January 17, 2018 1:53 PM

All replies

  • Hi Cheap Noodles,

    Thanks for your question.

    It is pretty hard to answer this without sample data. To solve your question more efficiently, would you mind typing out 5-10 rows of example data for these table, then showing what results you are expecting based on those sample data? It is much better if you can share the EXCEL work book. Do mask sensitive data before uploading.


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, December 29, 2017 1:33 AM
  • Thanks for the reply.

    I can't really share the data, plus it's pulling from an SQL server so it's a little difficult. I'll see if i can recreate the error on a flat sheet. Then I'll post it up.

    Again thanks for the reply

    Tuesday, January 2, 2018 10:37 AM
  • Hi Cheap,

    What is the formula you defined in DAX?

    Kind regards,

    Tom Sun

    Wednesday, January 3, 2018 5:26 AM
  • I have a tonne of measurements but no real DAX statements 

    Thursday, January 4, 2018 1:03 PM
  • Hi Wilson,

    I've managed to recreate the problem in a workbook. I have 2 pivot tables a regular one and PowerPivot one. The regular one outputs the correct amount of rows and the PowerPivot outputs more rows.

    I've marked the values in yellow on both pivots. Hopefully, this will illustrate the problem I'm having.

    Problem Pivot


    Friday, January 5, 2018 11:01 AM
  • Okay, for anyone reading this thread.

    Firstly support here isn't very good, lots of experts and no real answers.

    I trawled the web to find a solution for my problem and there appears that PowerPivot behaves differently when it comes to handling dates.
    If you split the date into year and month you have to create a hierarchy with PowerPivot for it to out the correct data.
    But and there is always a but, the PowerPivot table can no longer separate these two fields. So the year can't be kept in the filters whilst the months are in the row headers.

    Make sense why PowerPivot hasn't taken off.

    • Marked as answer by Cheap Noodles Wednesday, January 17, 2018 1:53 PM
    Wednesday, January 17, 2018 1:53 PM