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.
Anything related to PowerPivot and DAX Formuale
1 post • Page 1 of 1
Who is online
Users browsing this forum: No registered users and 2 guests