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


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

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.


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.


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


New Member
Feb 3, 2014
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:

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

Latest member

This Week's Hot Topics