Ratio of Columns Row by Row

[Anonymous]

Hello Power Bi Community,

I am trying to create a variable that takes the ratio of 2 values (No and Yes) at each value of Name. At the bottom in the total row I would like it to be the average of the ratios not the ratio of the sum of the 2 columns. Both No and Yes are calculated columns representing the count of No and Yes responses for each person. To score them I need to use a ratio and to accurately compare them to their peers I need the average score. I also have a filter value of Group where one person can be a part of different groups at different times. I need the average ratio to update accordingly if a filter is being used.

Here is what I attempted

Ratio is a column using the divide function. Ratio 1 is a measure using the divide function but because it divides by 0 it leaves B blank while an 8:0 ratio is important to recognize. Ratio 2 is a measure using an if statement saying if Ratio 1 is blank use the value for No. For the total in both measures it is using 45/9 = 5 what I am looking for is (5.5+8+4+14+0)/5 = 6.3.

I do not know if what I am wanting can be done but I appreciate anyone’s help.

Thanks!

[jthomson]

I'd try having a play with AVERAGEX in conjunction with your existing formula, that should make your total row work

edit - it will, but you need to put an IF statement in to handle your division by zero cases:

[Anonymous]

Hey jthomson thanks for your reply.

I attempted your solution as both a column and a measure and did not end up with the same result for some reason. Any clue as to why?

[Anonymous · Accepted solution]

@Anonymous,

Create the measure using DAX below and check if you get expected result.

Measure 2 = IF(COUNTROWS(VALUES(Table[Name]))=1,[Ratio 2 Original],SUMX(VALUES(Table[Name]),[Ratio 2 Original])/DISTINCTCOUNT(Table[Name]))

Regards,
Lydia