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

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

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,102,302
Messages
5,486,054
Members
407,529
Latest member
netojose

This Week's Hot Topics

Top