Using a filter to filter two different tables

[Anonymous]

I have a table which forecasts the units of each model that will be sold and a separate table which forecasts the volume of each model that will be sold. I also have a table which is much more complex and gives a detailed breakdown of all the products sold according to a specific region and customer and as well model. This table includes alot more models than the former table since the former tables only forecast for a particular category of models. 

My first two tables look like this:

REGION  Fin PRODUCT  FY20 BUDGET

1               X

1               Y

1               Z

2               X

2               Y

2               Z

3               X

3               Y

3               Z

REGION  Model          FY20 Units

1               X

1               Y

1               Z

2               X

2               Y

2               Z

3               X

3               Y

3               Z

The other table(which details our actuals),a breakdown of the product and its parts sold but it has a region and a model column which does coincide with the columns from the previous table, although it has a greater variety of models than that of the previous table.

I'm using a clustered column chart to compare the actuals vs the forecast and i want to have a model filter. I've tried to create a R/S between the three tables but only one of the columns managed to get filtered by the model filter. Is it possible for a model filter to work on both columns.

This is the relationship I have so far.

1 and 2 is the forecast in units and in volume whereas 3 is the detailed breakdown of our sales/actuals.

Thank you for your help!

[mussaenda]

@Anonymous,

So your expected output is?

Please provide a sample data and expected output to help you better

[Anonymous]

Hi,

@mussaenda 

This is the sample data.

https://drive.google.com/file/d/189AHJX6i0K 0fO5Qx40O7CZa8ZeKdM7gD/view?usp=sharing

As you can see in the pic and in the pbix, i need two separate filters to make sure that each column is filtered down to the chosen model. Is it possible to have one filter only? So that both columns in each chart will be filtered correctly.

Thank you!

[mussaenda · Accepted solution]

@Anonymous,

Try this modified pbix of yours if this gets your desired filtering

https://gofile.io/?c=k9rBwJ

[Anonymous]

Hi @mussaenda ,

This worked well when we wanted to filter the actuals and budget diagram(in terms of units). But we've noticed that the filter isnt working on the budget portion of the other diagram(revenue actuals and budget). Is it possible for the filter to work for both diagrams and both columns for these diagrams?

Thank you!

[mussaenda]

@Anonymous,

Yes.

 Create a relationship on the tables.

I just created relationships on the tables you required before that's why it is not reflecting on the revenue table.

[Anonymous]

@mussaenda 

Yes we did. However, when we do create a relationship it ends up being a dotted line.

[Anonymous]

Thanks! This worked well.

[JPY]

Hi @mussaenda,

Thanks for your answer! 

It works well 🙂

Regards,

Joyce