Display Data From Different Tables on a Matrix Visual

[alottawarmh]

Hello everyone, I have a problem with the matrix visual. My data is coming from 2 different tables: one for budget the other one is for actual quantity. Both tables have the exact column names: these are service type, firm name, quantity and period. What I want is, I want to show the budgeted quantity and the actual quantity on a single matrix. Below visual shows the matrix that I want as an example.

And following tables represent my data tables. To make a relationship between these 2 tables, I have created a merged column for both of them on query editor. Those merged columns combines the column values of service type, firm and period. 

On the matrix visual, from the budget table I put the period as column, service type and firm as rows. As for the value, I put quantity from the both tables. The budget quantity is showing correctly, but actual quantity is shown as the total. Actual quantity is not filtered by period or service type or firm.

Do you have any suggestions for my case? Maybe it is a very simple question but I'm new to Power BI so I would really appreciate any kind of help. Thank you!

[Anonymous · Accepted solution]

I have recreated your case with a sample in Excel and created the matrix as you expected in a Power BI. Feel free to manipulate it and review the Power Query. I believe the issue may be related to the relationship.

Download the files from this link 🙂 

[alottawarmh]

You are right, probably the relationship was the problem. I did not merge my data tables before, the tables have much more columns than what I showed on my first post and I thought it would be complex. But after your solution I tried and it worked! Thank you so much for the help 😊