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

Lasselakan

New Member
Joined
Feb 3, 2014
Messages
3
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/1820936/model_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
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

scottsen

Well-known Member
Joined
Mar 16, 2014
Messages
1,263
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?
 

Lasselakan

New Member
Joined
Feb 3, 2014
Messages
3
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).
 

Watch MrExcel Video

Forum statistics

Threads
1,101,849
Messages
5,483,293
Members
407,391
Latest member
sumantskj

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top