Forum Discussion
Calculate difference from the average
Dear All, @
I am working on a table having five columns containing numeric values.
I am interested in calculating the average score of an individual based upon those five columns. Then, I would like to calculate
Total score individual / Avg score
and then plot it.
Any help will be appreciated.
Regards,
Umer
Hi mukhan
to get the average, you could duplicate the initial query ( assume name table2), group by the individual ID and calculate the average (assume column name is avgcolumn) .
for the total, in the first query by creating a measure:
Measure =
var _totalscore =Calculate( sum('Table'[ColumnName]) , All (Table))var _avgscore =selectedvalue('Table2'[AvgColumn])
var _output = _totalscore/_avgscore
return
_output
plotting the measure on values with individual id/name, might give the result.
If it doesn't ,could you provide sample input data and sample output removing sensitive data ?
Appreciate a thumbs up if this is helpful.
Thanks
4 Replies
Hi mukhan
to get the average, you could duplicate the initial query ( assume name table2), group by the individual ID and calculate the average (assume column name is avgcolumn) .
for the total, in the first query by creating a measure:
Measure =
var _totalscore =Calculate( sum('Table'[ColumnName]) , All (Table))var _avgscore =selectedvalue('Table2'[AvgColumn])
var _output = _totalscore/_avgscore
return
_output
plotting the measure on values with individual id/name, might give the result.
If it doesn't ,could you provide sample input data and sample output removing sensitive data ?
Appreciate a thumbs up if this is helpful.
Thanks
You'll likely want to unpivot those 5 columns. Sample data (inserted as a table, not an image) and expected output will help you get a specific solution.
Pat
