topic Re: calculate average of two columns and then score them by 1 or 0 if the average is >14 in DAX Commands and Tips

calculate average of two columns and then score them by 1 or 0 if the average is >14

finley0726 — Thu, 20 Jul 2023 19:09:16 GMT

hello, i have two columns of data- I would like to sum two columns and take their average in each individual row(I thought this was AVERAGEX). . After the average for each row is calculated, I would like to create a measure that gives the row a score of 1 if the average is >14, otherwise a score of 0.

Re: calculate average of two columns and then score them by 1 or 0 if the average is >14

AnthonyGenovese — Thu, 20 Jul 2023 19:15:50 GMT

It would be helpful if you created a table to show us the data you have, and how you want the results to look like. Also, do you want this as a calculated column?

If this post was helpful, please kudos or accept the answer as a solution.
~ Anthony Genovese
Need more PBI help? PM me for affordable, dedicated training or consultant recomendations!

Re: calculate average of two columns and then score them by 1 or 0 if the average is >14

finley0726 — Thu, 20 Jul 2023 19:24:49 GMT

code	column1	column2
1	5	50
2	1	1
3	2	25
4	14	3
5	10	3

I want the average of (column 1 + column 2) for each individual code. then, if the average is >14, score is 1 otherwise 0.

for the average, I used a measure:

Measure = averagex('table','table'[column1] +'table'[column2])

When I try to visualize the data in a matrix with Code in 'Rows' and my measure in "values' it shows the same value no matter the code. I took a PBI online course and I vaguely remember that this is a common issue but I cannot remember for the life of me how to fix it. Sorry, fairly new to this.

Re: calculate average of two columns and then score them by 1 or 0 if the average is >14

tamerj1 — Thu, 20 Jul 2023 19:54:05 GMT

Hi finley0726

please try

ScoreMeasure =
SUMX ( 'table', INT ( ( 'table'[column1] + 'table'[column2] ) / 2 > 14 ) )

Re: calculate average of two columns and then score them by 1 or 0 if the average is >14

AnthonyGenovese — Thu, 20 Jul 2023 20:02:22 GMT

Try this

Measure = var myAvg=SUMX('Table', 'Table'[Column1]+'Table'[Column2])/2

var result = if(myAvg>14,1,0)

return result

If this post was helpful, please kudos or accept the answer as a solution.
~ Anthony Genovese
Need more PBI help? PM me for affordable, dedicated training or consultant recomendations!