Calculate difference from the average

[mukhan]

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

[ppm1]

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

[mukhan]

Thanks for replying. How can we embed pbix to the topic?

[ppm1]

You can insert the data by adding a table from the ribbon, or you can upload your pbix to OneDrive or Google Drive and share link here.

Pat

[adudani · Accepted solution]

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