Blank Values in Pivot Table Calculation

Anything related to PowerPivot and DAX Formuale
Posts: 1
Joined: Wed Jul 08, 2020 6:25 am

Blank Values in Pivot Table Calculation

Postby smr » Wed Jul 08, 2020 7:01 am

The attached pivot table has two fields with year-over-year values, named Total Handle and Total Handle PY. Think of handle as sales. So sales year-over-year. There is a Sales table (data in a tab in the sheet), a Calendar table, and a Signal table. I started without the Signal table, but ran into an issue, blank values, that I cannot explain. My objective was to look at the YOY sales, and compute a tax amount by taking the [$ YOY] and multiplying by .01 if Signal Type = "S" and by .0125 if Signal Type="X", The measure [Tax Amount SUMX Sales] results in blank values for months that had no data in Apr and/or May 2020. This is using only the Sales Table and Calendar Table.

However, if I add the Signal Table, and use the measure [Tax Amount SUMX Signal], no blanks and all looks good.

Also, if I add a record in the sales table for the missing months with a value of 0, no blanks.

I'm trying to understand why the blanks. No change in incoming filter context. Something about how the workings of the two measure differ.

I got to this forum reading Matt Allington's article "When to Create a Lookup Table". It seems to me I shouldn't have to in for this simple model, but it works when I do.

Any insight would be appreciated. Thanks.
Power Pivot Blank Values.xlsx
(501.68 KiB) Downloaded 9 times

Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 2 guests