Creating Target line with multiple column values

[shubri]

Hello, I am struggling to create a bar graph which has last year values compared to this year values plus desired target. 

I have 5 columns for each value and the main issue is, when I make selection where values  arent present, the trend line and past year data messes up as it recalculates it up to 100%.

So far I have created bar charts, previous and current year and I have added another table with target values for each category.

And it works ok, until you try to drill down and select something that has no one or more categories, lets say, it misses 3 and 4. 

This is the data example, it just a count of five different values in the column:

value, count

1 5

2 10

3 50

4 60

5 100

If selection is made against subject without some of the values, it completely scramles the graph, leaving it with only values from that subject. 

I would need past year and trend line to be static, regardles of selection. 

Thanks, 

[v-caliao-msft]

@shubri,

I would need past year and trend line to be static, regardles of selection. 

Based on your requirement, you could use a Line and clustered column visual, and add your trend of last year to Line values.

If this is not what you want, please provide us more information about your report, so that we can make further analysis. You can upload a pbix file to Onedrive or any web storage and share the link. Do mask sensitive data before uploading.

Regards,
Charlie Liao

[shubri]

Hi Charlie , thanks for lookin into!

let me try to put this in other way. Issue with the line is that it follows the selection.

I have made one example, on the left is when selection has all the values in it, from 1 to 5. On the right, when I select department or manager that might not have all the values present, in this example 1, 2 and 5 arent present, the target line drops that values also and recalculates its values so it shows 100% at all times. I would like to keep it as on the left, but be able to choose departments/managers without some of the values, its quite normal that you do not have full range of values in your team. 

When all data is presentWhen department without 1, 2, and 5 is selected