cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Rajeshaxxonet
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
Ashish_Mathur
Super User
Super User

Hi,

 

You may download my PBI file from here.

 

Hope this helps.

 

Untitled.png

 

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

affan
Solution Sage
Solution Sage

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

 

 

 

 

 

 

 

 

View solution in original post

5 REPLIES 5
v-piga-msft
Resident Rockstar
Resident Rockstar

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.
affan
Solution Sage
Solution Sage

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

 

 

 

 

 

 

 

 

Ashish_Mathur
Super User
Super User

Hi,

 

You may download my PBI file from here.

 

Hope this helps.

 

Untitled.png

 

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
itchyeyeballs
Impactful Individual
Impactful Individual

drewlewis15
Solution Specialist
Solution Specialist

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

 

 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.