Return column name with highest average for selected columns

[Anonymous]

Hi Powerbi Community,

I'm looking for help with a specific issue I've yet to figure out.

I have data that I'm putting together and I have 5 columns with individuals values, for example, they are ranked 1-5.

I want to find the average of each column and return the column name with the highest average.  

I have tried an index column, but have been unsuccessful in pulling the column name with the highest average into a card.

I've included two columns as an example, so if I were to use the average function on each column as a measure, how would I write a measure to bring back the name of the individual column with the highest value.

I appreciate any help you may be able to offer!

[Anonymous · Accepted solution]

@Anonymous 

Please try follow the steps:

1. hold shift select the 2 columns => unpivot in query editor.

2. Create the measure to compare the averages, you will need multiple if you have more than 2 columns.

highest = 
var AVG1 = CALCULATE(AVERAGE('Table'[Value]),FILTER('Table',[Attribute]="Column1"))
var AVG2 = CALCULATE(AVERAGE('Table'[Value]),FILTER('Table',[Attribute]="Column2"))
Return
IF(AVG1>AVG2,MAX([Attribute]),MAX([Attribute]))

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

HI @Anonymous 

Do you need average or the highest value. How do u want to "name of the individual column"?

Please paste sample input with all columns and some rows and output based on the sample input you are looking to achieve.