In the pivot table lot of products (column MRDR) appeared for which we have no data in the sales table:
Important to know that not all the products from masterdata table are represented in the sales data table (we have old products, technical Product IDs, products listed in other countries, for which we have no sales).
Our real-life database consists of two main tables: sales data by product (Product ID, turnover, volume, etc.) and product masterdata (Product ID, category, brand, etc.). These two tables are linked by Product ID.
Medical Student: But wasn't that the whole basis of your grandfather's work, sir, the reanimation of dead tissue?
Freddy: My grandfather was a very sick man.
by The FrankensTeam
While building up my first PowerPiovot database, I realized a strange problem: tons of blank rows appeared in the final pivot table. Here I would like to share with you the details and how we can avoid it.
Hungarian version of this post is available here.
In the particular case it means thousands of rows mixed with rows with values. Obviously I did not want to give this "sick" report with "dead" codes to the users.
I put together a small example to make it clear.
And the sales data table:
I loaded both tables to PowerPivot and joined with the Product ID field.
You can see products number 7,8,9,10 are not represented in the data table. These products (and only these) belong to Product group x and y.
I created the first calculated field for turnover growth (I used summarized measures):
Looks to be correct. But in our company the definition of Growth is different: we show only the value above 100%, so I have to subtract one:Using this new growth rate the pivot shows the groups without data too:
Clearly the problem is the -1 constant value at the end of the formula.
I tried with IFERROR too:
The result is the same: pivot contains "dead" products.
The only way I managed to eliminate the unnecessary rows is to change the calculation formula this way, using no constant value:
The problem is generated by the calculated field: in PowerPivot BLANK + constant = constant, so the calculation gives a result for the BLANK rows (these are non-BLANKs anymore) and they will appear in the pivot table.
DottorGábor: if we think with SQL codes, it seems as if the JOIN type had been changed: now it is OUTER JOIN instead of INNER JOIN. It's creepy because our pivot shows data which does not exists - only as a result of the way we built up the calcuation formula!
if you would like to take a look at the example file, you can download from here.