Stacked column chart that ignores certain filters

[Mortiq]

Hi guys,

I'd like to compare actuals with a target, using a stacked column chart.

The target values should always remain the same, but the actuals should be changed depending on the filter settings for the category column.

This is the data table I am working with.

This is what the chart looks like when no category filter is applied.

This is what the chart is supposed to look like when the category filter is set to 2. You can see that the target values remain the same, only the actuals change.

This is what the chart actually looks like. 

How do I make this work? I have tried using CALCULATE() and SUM() to no avail and tried using two visuals, but the borders are just too big. 

Thanks for any tips for a newbie. 🙂

[amitchandak]

@Mortiq , Create an independent table for the category and use that to filter category

calculate(Sum(Table[Cost]), filter(Table, [Actual vs Target] ="Actual" && [Category] in values(category[category]) ))

calculate(Sum(Table[Cost]), filter(Table, [Actual vs Target] ="Target"))

[Mortiq]

@amitchandak  Thanks for the quick reply.
I created an independent table and defined two measures, but the results are the same as before.

And how would I use two measures to display in one stacked column chart?

Actuals = CALCULATE(sum(tbl_costs_actual_vs_target[Costs]),FILTER(tbl_costs_actual_vs_target,tbl_costs_actual_vs_target[Actual or Target]="Actual" && tbl_costs_actual_vs_target[Category] in values(tbl_Category[Category])))

Target = CALCULATE(sum(tbl_costs_actual_vs_target[Costs]),FILTER(tbl_costs_actual_vs_target,tbl_costs_actual_vs_target[Actual or Target]="Target"))

[amitchandak]

@Mortiq , In Stack use axis and two measure, no legend, that should work

[Mortiq]

That did not work, for some reason, but I found a way to make the background of the visual transparent and that did the trick just fine.

Thanks nonetheles! 🙂