I am using a tabular model with measures to actual and plan as well as the variance. I use a Date table to display in a line chart the progress throughout this year by month.
I also use a table visual to show current month to date variance of actual vs plan by location.
In summary, I have one line chart visual with all the months of the year and two lines for actual and plan
I also have a single table visual with each location in the rows and the value is the current months variance of actual to plan
My Chart lines are based on these measures
Current Year = CALCULATE([Prod This Year Value],filter(DateDimension,DateDimension[Year Specificity]="This Year"))
Current Year Plan = CALCULATE([Prod PKG Plan Value],filter(DateDimension,DateDimension[Year Specificity]="This Year"))
My Table contains this measure
%MTY vs Plan = DIVIDE([Current Month to Yesterday],[Current Month to Yesterday Plan],0)-1
When I click on a location in the table visual it is filtering the information in the chart to the current month only, I would like to the filter to be only the location and all months of the year remain.
How do I prevent the table measure filtering out all the other months in the chart measures while keeping the location filter in place
@MartynRamsden Thanks for the reply, unfortunately this is a solution I have already tried but tried again in the hope I had done something wrong. when implementing this solution the value for Current Year becomes the same for all months and it has no impact to the problem of the chart data being filtered to a single month when clicking on the table. I don't want to remove the interaction between the table and the chart because I do want clicking on the table to filter the chart to the the selected location.
@MartynRamsden unfortnately not, I am using an SSAS model and the measures are written in there so not visible in pbix. I will take some time later to duplicate using excel or something as I would really like to get this issue resolved.