How to View Actual and Budget values in single tables

[Rajeshaxxonet]

Hi All,

I have Table 1 and Table 2 and there is no relationship created in the model (This tables are transaction tables and not master tables).

Table 1 :

City	Actual_Population
Bangalore	10
Bangalore	12
Chennai	4
Chennai	5
Mangalore	8
Mangalore	8
Mangalore	8

Table 2 :

City	Budget_Population
Bangalore	15
Bangalore	15
Chennai	3
Chennai	6
Mangalore	9
Mangalore	9
Mangalore	9

I am trying to populate Actual and Budget values in the Matrix table. Actual data and Budget data is populated from Table 1 and Table 2 respectively.

Expected data output :

City	Actual_Population	Budget_Population
Bangalore	22	30
Chennai	9	9
Mangalore	24	27

How to achieve this through Power BI?

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[v-piga-msft]

Hi @Rajeshaxxonet,

It seems that the solutions from others could solve your problem.

If you have solved, please accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

Best  Regards,

Cherry

[affan · Accepted solution]

Hi @Rajeshaxxonet,

You can acheive the solution without adding a separate table for cities. With your exiting dataset you can get the following result.

Microsoft has already introduced Many to May relationships which help resolving such requirements.

If you don't find Many to Many (*:*) option in the cardinality you can turn on this feature as shown below.

In the options window trun on the 'Composite models' feature.

You can download the PBIX file here.

https://www.dropbox.com/s/99c3oegu3kp44l1/Many%20to%20many.pbix?dl=0

Please mark this post as an accepted solution if this helped you.

Regards,

Affan

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[itchyeyeballs]

Read this and the linked posts:

https://powerpivotpro.com/2012/01/salesbudget-integrating-data-of-different-grains/

[drewlewis15]

I would recommend simply adding a lookup table containing unique values of city to join these two tables.  From there, you would choose the city column from your lookup table and the actual/budget value columns from table 1 and table 2 in a "Table" visual.  See below: