Sum of measure with bidirectional relationship

[tt211595]

Hello! I have two example tables: A and B.

Table A:

personID	Name
1	Jane
2	John
3	Will
4	Sam
5	Laura

Table A:

personID	Car Color
1	Blue
1	Green
3	Blue
4	Blue

I'd like to have a bi-directional relationship between the two tables based on the column personID. I'd like to create a bar graph like below, showing that 60% of people (3/5 people) have a blue car and 20% of people (1/5) have a green car.

I've created a measure (so that it's responsive to my slicers) calculated as below:

Measure = DISTINCTCOUNT(b[personID])/COUNT(a[ID])

where the numerator is the number of people that appear in table B (three in this example) and the denominator is the total number of people (five in this example, but this could be affected by slicers). My issue is with the denominator; when I create this bar graph with a bi-directional relationship between the tables, the bars show 100% because the COUNT(a[ID]), or the count of total number of people, gets broken down by car color (i.e., I want the calculation for "Blue" to be 3/5, but it's getting calculated as 3/3).

I don't have this issue if I use a single-direction relationship between the two tables, but I would prefer a bi-directional relationship so that I can cross-highlight when clicking on the bars. I'd appreciate any advice on how to do this; my first thought was to sum up the measure, but that doesn't seem possible without breaking down the counts by color.

Thanks in advance!

[v-zhangti · Accepted solution]

Hi, @tt211595 

You can try the following methods.

Measure = 
var _a = COUNTROWS(FILTER(ALL('Table B'),[Car Color]=SELECTEDVALUE('Table B'[Car Color])))
var _b = CALCULATE(DISTINCTCOUNT('Table A'[personID]),CROSSFILTER('Table A'[personID],'Table B'[personID],OneWay_LeftFiltersRight))
return DIVIDE(_a,_b)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[tt211595]

Yes, exactly. Thank you so much!