Suppose I have a table with the following columns:
- Year (varying from 2000 to 2023)
- Class ("items bought", "items sold", "remaining items" etc.)
- Value (of the variable in the year)
- Country
I have two filter/selection windows where the user can choose the Year(s) and the Class to be charted. And I want the chart shows the sum of the Value column by Country, for the chosen Year(s) and Class, Countries.
However, it doesn't make sense to sum things like items remaining. So, if the user choose the Class "remaining items" (or some similar Class), I want that the chart shows only the sum of the Values, over all Countries, of the last (higher) selected Year.
How can I achieve it?
I created a column Flag, which is 1 when Class is "remaining items" or similar and 0 otherwise. After, I created this column:
Test = IF(AND('MyTable'[FLAG] = 1,'MyTable'[YEAR] < MAX('MyTable'[YEAR])), 0,'MyTable'[VALUE])
After, I created a pizza chart with Values = Test and Details = Country. For Classes with Flag = 0 the chart works well. For Classes with FLAG = 1 the chart works when 2023 is select but shows nothing (only a blank window) when only earlier years are selected. This means that the condition 'MyTable'[YEAR] < MAX('MyTable'[YEAR]) is comparing with the "absolute" highest year (2023) rather than the "relative" highest year (the highest between the selected ones).
I also created a measure which succesfully returns the "relative" highest year:
MaxYear = CALCULATE(MAX('MyTable'[Year]))
But when I tried to combine this measure with the column Test, it doesn't works.
How can I solve this problem and make the pizza chart shows the correct values when a Class with Flag = 1 is selected?