Selective filtering for two tables

[Anonymous]

I am new with Power BI and hope someone here could help me with the issue I have.

1) I have my Forecast table that includes forecast for JULY and AUGUST. Forecasts were made in May and June (FC-May & FC-June). 

2) I also have the Actual Sales table where I see the real sales for each item in JULY and AUGUST. Note that I forecasted 5 Papaya for AUGUST but instead 20 Grapefruit was sold without even being forecasted.

FORECAST TABLE (5 Papaya forecasted for AUGUST)

Forecast Creation Month	Sales Month	Product	Forecasted Sales
FC-May	JULY	Apple	10
FC-May	JULY	Orange	10
FC-May	AUGUST	Apple	10
FC-May	AUGUST	Orange	10
FC-June	JULY	Apple	20
FC-June	JULY	Orange	20
FC-June	AUGUST	Apple	20
FC-June	AUGUST	Orange	20
FC-June	AUGUST	Papaya	5

ACTUAL SALES TABLE (20 Grapefruit sold in AUGUST, no Papaya sold)

Sales Month	Product	Actual Sales
JULY	Apple	8
JULY	Orange	8
AUGUST	Apple	13
AUGUST	Orange	13
AUGUST	Grapefruit	20

My problem starts when I want to compare the total number of items in AUGUST from the Actual Sales table, with the forecast I made in June (FC-June). When I put Actual Sales and Forecasted Sales into a Clustered Column Chart and filter the Forecast Creation Month to FC-June I do not get the total sales of August any more because Grapefruit was not in the forecast.

Is it for example possible somehow to set a filter in my chart to only apply to one of the Values in the chart?

I would really appreciate if someone could help me out, many thanks!

[v-yingjl · Accepted solution]

Hi @Anonymous ,

Is this your expected output?

If so, you can refer my sample file: Selective filtering for two tables.pbix

If not, please kindly let me know.

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Great thanks a lot @v-yingjl for the example, I got it now!

[parry2k]

@Anonymous as a best practice, you should have another table with a unique list of foods, and then set relationships from that table with your these two tables and in visualization use fruits from this common dimensional table and sum from the respective table and everything will work.

Read this post for modeling

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡