I have the following table:
I'd like to change it so that if there's no data for and product group which doesn't have sales this year but does the previous year the record doesn't get displayed. In this case 'Other' wouldn't be displayed. How do I do that?
If it helps the formula I used for 'Actual Prior Year' is:
Actual Prior Year:=SUMX(FILTER(BI_SalesAnalysis, MONTH([Delivery Date]) = MONTH(TODAY()) && [Financial Year] = MAX([Financial Year]) - 1), [Delivery Value CON])
Since you are calculating a column, it will be calculated for all rows in the table showing with your applied filters.
What you need to do is change your filters on your display table to not show rows without an [Actual] for the month. Then you will have to make sure of what you want as far as math on the bottom. If you still want the total to show correctly for the Actual Prior Year, you will have to use ALL in your calculations - otherwise that 200pounds would be removed when you filtered out the "Other" category.