Representing Retention Bands (Using measure in axis)

Hi Dedmon,

I have created an example as there was lots of other non related data in the real one - but the copy demonstrates the same 'issue' I've uploaded it to: https://drive.google.com/file/d/1zaL1t_0pI5Y7dq4wsFHASzgE9ibXqq7l/view?usp=sharing

You should see a bar chart which uses a measure to count the salesperson and then a grouping column from the salesperson table. 

It works fine only in the scenario where a user does not change the 'FiscalYear' filter. You can see the number of salespeople who fall into each retention band and if you click the bar you get the list of salespeople who fall into that band in the matrix below.

However because of the data (and structure) this only works because its using a banding on the salesperson table which is a summary of all the data. As expected the bar chart doesn't update when the FiscalYear filter is selected as there is no Fiscal Year date on the salesperson table - if I did try something like that then the salesperson rows would no longer be unique on that table (as we would have to break down their retention by multiple years). Alternatively one could create extra columns for each year on the salesperson table (an example for 2021/22 is included in the pbix) but I can't see how that can work in a user friendly way as that would mean creating a bar chart for every single year/date etc I would want to represent.

Of course as the matrix uses measures from the opportunities table it updates appropriately when the year is selected, but I hope to be able to apply that to the bar chart if possible.

Hope my example file helps with understanding. I added a 'Retention Bands' table as well as I did see it mentioned in a couple of other example but I'm not quite sure how to apply it to solving this issue as usually the 'bands' table did something simple like a sum of a field.