cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Rajeshaxxonet Frequent Visitor
Frequent Visitor

How to View Actual and Budget values in single tables

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 :

 

CityActual_Population
Bangalore10
Bangalore12
Chennai4
Chennai5
Mangalore8
Mangalore8
Mangalore8


Table 2 :

 

CityBudget_Population
Bangalore15
Bangalore15
Chennai3
Chennai6
Mangalore9
Mangalore9
Mangalore9


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 :

 

CityActual_PopulationBudget_Population
Bangalore2230
Chennai99
Mangalore2427

 

How to achieve this through Power BI?

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: How to View Actual and Budget values in single tables

Hi,

 

You may download my PBI file from here.

 

Hope this helps.

 

Untitled.png

 

 

affan Established Member
Established Member

Re: How to View Actual and Budget values in single tables

Hi @Rajeshaxxonet,

 

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

city population.png

 

 

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

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

Options.png

 

 

 

 

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

Preview features.pngYou 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

 

 

 

 

 

 

 

 

5 REPLIES 5
Highlighted
drewlewis15 Member
Member

Re: How to View Actual and Budget values in single tables

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:

 

join.png

 

table.png

 

final.png

 

 

itchyeyeballs Established Member
Established Member

Re: How to View Actual and Budget values in single tables

Super User
Super User

Re: How to View Actual and Budget values in single tables

Hi,

 

You may download my PBI file from here.

 

Hope this helps.

 

Untitled.png

 

 

affan Established Member
Established Member

Re: How to View Actual and Budget values in single tables

Hi @Rajeshaxxonet,

 

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

city population.png

 

 

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

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

Options.png

 

 

 

 

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

Preview features.pngYou 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

 

 

 

 

 

 

 

 

Community Support Team
Community Support Team

Re: How to View Actual and Budget values in single tables

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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.