PowerPivot: Constant in calculated field results blank rows

posted Jan 12, 2016, 8:55 AM by Krisztina Szabó   [ updated Jan 15, 2016, 9:58 AM ]

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.

Database

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.
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).

The problem

In the pivot table lot of products (column MRDR) appeared for which we have no data in the sales table:


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.

Investigation

I realized the report contains data for these "dead" codes only in calculated columns, where I used a constant value in the DAX formula. (In the above picutre TMI% column is a calculated one.)
I put together a small example to make it clear.

Here is the product masterdata table:


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):

Growth % 1:=   DIVIDE( [turnover 2015] , [Turnover 2014] , 0 )

And here is the pivot table showing Growth % 1 by Product groups:


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:

Growth % 2:=  DIVIDE( [turnover 2015] , [turnover 2014] , 1 ) - 1

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:

Growth % 3:=  IFERROR( [turnover 2015] / [turnover 2014] -1 , 0 )

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:

Growth % 4:=  IFERROR( ([turnover 2015]-[turnover 2014]) / [turnover 2014] , 0 )

What happened?

Based on the join between the Product IDs, Excel builds up the calculation background table, containing ALL the Product IDs, but BLANK rows (without data)  will not appear in the final pivot table.
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!

Solution

Well... I do not know what is the reason behind this strange way of using BLANK, it only makes problem for me. Fortunately the measures we use can be changed in a way without constant - this is the only solution I found for the problem.

if you would like to take a look at the example file, you can download from here.



Comments