Hoping to get help with the following issue:
I want a measure that creates virtual table to return all products and customer ids in the first two columns (ignore any slicers) and then take Current year units measure and prior year units measure (based on date slicer on dashboard) and the last column subtracts the two measures. The result is a sumx on the subtracted column.
I know the simple solution is to just use calculate but given this is part of a bigger measure I’m working on, it needs to be done using sumx and summarize as I need to perform a computation on every row of the virtual customer-product pair.
The issue I’m facing is that if a user selects a fiscal year 2017 (current year) as a slicer, the product and customers showing in the virtual table are only 2017. Hence if there were units in 2016 and not in 2017 it will ignore this and I don’t want it to. It should show all products and customers and then use the filter context to get current year (2017) units and prior year (2016) units for each product customer grouping. Then subtract the two columns on a row level and finally add the final result. Help is much appreciated.
Please see attached file for more details on the issue.
Summarize Sumx Help.pbix (383.7 KB)