Compare actual vs planned

[Microbz]

Maybe a rookie question, but then again I am a rookie :). I have two tables "Plan" and "Dashboard" both of which have a column for product type (PRODUCT_CODE/PLAN_CODE), amount of product (PRODUCT_KG/REC_PROD_KG) and date. The "Plan" table contains the monthly goal for each product type in kilograms. The "Dashboard" table contains the actual product amount per production batch per product type. I have a third created table, CALENDAR,  which I used to create date relationship between "Dashboard" and "Plan" tables. I want a bar graph that displays the planned product sum for each product type and actual sum of product produced with the graph sliced on date (CALENDAR). The date slicer works fine for individual graphs of each, planned and actual, but when I put them on the same graph the planned defaults to total product amount by product type for entire Plan table. I thought I needed a relationship for product type (i.e. PLAN_CODE & PRODUCT_CODE), but this is a many-to-many and PBI doesn't like that. I tried creating a seperate PRODUCT_CODE table and set up relationship to Plan and Dashboard tables then used PRODUCT_CODE table as the x-axis for bar graph but no luck. Any suggestions?

[Microbz]

Thanks @amitchandak , I was thinking (hoping?) to maintain as two independent tables but in the end merging was easiest. Cheers!

[amitchandak · Accepted solution]

@Microbz , If plan_code and product_code have the same values. You need to create a new table using plan code and product code join with both tables and use it

new table

new dim = distinct(union(distinct(plan[plan_code]),distinct(dashboard[product_code])))