Combining measures from two tables is not granular for both measures

[monibuw]

I want to create a metric (ratio) using two measures in different tables. The two tables are joined on location name (for lack of a better primary key). One of these tables (Table 01) is an aggregate table that I created using GROUPBY feature (not sure if this is relevant here).

When I create the visual (table) in Power BI combining the two tables, I get the breakdown of population of each location properly (because I am starting off with Table 01 - aggregate table) and when I add the measure (FacilityScore) from Table 02, I get the aggregate of that measure rather than break down by location. 

Below is a screenshot that explains the whole scenario:

I am sure there is a way to do this, but I am not that skilled at DAX or other solutions in Power BI. 
I appreciate some help.

[v-robertq-msft · Accepted solution]

Hi, @monibuw 

According to your description and sample pictures, I can simply achieve your requirement using two calculated columns, you can try these calculated columns:

First, you should give the two tables a one-to-one relationship with the “Both” filter:

T02.FacilityScore =

RELATED('Table 02'[T02.FacilityScore])

Ratio =

DIVIDE([T01.Pop],[T02.FacilityScore])

Then set the column type of this column to a Dedicated number:

And create a table chart to place the columns, you can get what you want, like this:

You can download my test pbix file below

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jihwan_Kim]

Hi, @monibuw 

I am not sure how your data model and the direction of the relationship look like.

One way is to create a bi-direction relationship. However, in almost all cases, this is not the prefered way.

Another way is to create a dim-location table, and create a relationship between dim location - table 1 and dim location - table 2. And put the location column from the dim-location table to the visualization.

In this way, I think your measure will work.

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[PoojaDarbhe]

Hi @monibuw ,

Please try the steps below,

1) Create an active relationship between table 1 and table 2 on location column

2) Now create a measure 

test = SUM(Table 01[Pop]) / SUM(Table 02[facility score])

3) Pull location from one of the table, then facility score, pop and new created measure

It will give you an output as expected.

Let me know in case you are facing some dificulty in implementing above.

Best regards,

Pooja Darbhe