Multiple lookup-tables different for Pivot Table vs. Power View
Results 1 to 3 of 3

Thread: Multiple lookup-tables different for Pivot Table vs. Power View
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Feb 2014
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Multiple lookup-tables different for Pivot Table vs. Power View

    Hi
    I have a model with multiple many-to-many relationships between table columns. I've managed to solve this and get the wanted result when using Power View, but it's different when using conventional Pivot Tables. However, if applying a Calculated field (product -> Calculated field 1) it becomes correct, but I feel that I'm not doing this right and that it could be done in some smarter way.

    I find it pretty much impossible to describe in words so I've made a demo-file to illustrate my issue.
    https://dl.dropboxusercontent.com/u/...odel_test.xlsx

    First tab ("Power View1") shows 3 slicers with a table below. The table shows what I'm expecting for any combination of slicer usage.

    Second tab ("Pivot Table1") also shows 3 slicers with a table below. The table does not show what I'm expecting, but if I add product -> "Calculated field 1" to VALUES it does. I made this Calculated field on my own idea that I simply need some formula that includes both dimensions, but that's just my guess.

    Third tab ("raw_data") shows all raw tables (which are then Linked Tables to the Data Model).


    Please if anyone could guide me on how to make proper use of a Calculated field (or some other solution) on second tab to make the Pivot Table behave like the Power View. Because I need Pivot Tables rather than Power View because the Print-functionality of Power View sucks in Excel Online.

    /Lars

  2. #2
    Board Regular scottsen's Avatar
    Join Date
    Mar 2014
    Location
    Seattle, WA
    Posts
    1,263
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Multiple lookup-tables different for Pivot Table vs. Power View

    The behavior that you are seeing is that if you have NO values on the pivot table, it doesn't bother trying to filter. However, if you have a least one value in there... it removes rows where all the values are BLANK().

    In your case, if you used say... COUNTROWS(product_to_store) on values, you would get the same results. Typicallly you want to see SOME value in your pivot table... What are those values in your case?
    scott at tinylizard.com : Power Pivot Consultant, Trainer and Mercenary
    See my Power Pivot blog at http://tinylizard.com/blog

  3. #3
    New Member
    Join Date
    Feb 2014
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Multiple lookup-tables different for Pivot Table vs. Power View

    Thanks!
    Ok, I guessed so. Let's say I want average product's price as Value. So if I add it it still doesn't work. But if I add a Calculated field:
    =IF(ISBLANK(COUNTROWS(product_to_color)*COUNTROWS(product_to_store));BLANK();AVERAGE(product[price]))

    then it works as expected. But I guess I'm not doing it the proper way!?

    (The file is updated so you can check it again).

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •