How can I calculate the Average of Data values across multiple columns?

[ERing]

I have data related to customer survey responses for service representatives. My table visual below displays each Representative and the average of thier scores for each category.

I've been given a new requirement which is to display the Average of scores for each "Score_Categeory". The first 16 columns in the table visual belong to the "Process" Score_Category and the last 4 columns belong to the "Skills" Score_Category.

You'll notice that each Opportunity_ID in my data has two rows. The first row contains scores related to the "Process" Score_Category and the second row containts scores for the "Skills" Score_Category.

My expected output is to have two new measures that I can add as columns to my table visual. The first measure will be the Average of all the "Process" scores for a representative. The second measure will be the Average of all the "Skills" scores for a representative.

I think it's important to note that the measures need to come from the raw data table, not the table visual output. In other words, the new average measures need to be the average of all scores for the relevant Score_Category across all Opportunity_ID's for a representative.

Sample Data PBIX 

[v-mdharahman]

Hi @ERing,

Thanks for reaching out to the Microsoft fabric community forum.

It looks like you want to create separate measures that can be added as columns for your table visual. As @Ashish_Mathur, @danextian, @ajaybabuinturi and @pankajnamekar25 all already responded to your query, kindly go through their responses and check if it solves your issue. Also if your issue has been resolved please mark the helpful reply as solution so that other community members facing the same issue can find the solution easily.

I would also take a moment to thank @Ashish_Mathur, @danextian, @ajaybabuinturi and @pankajnamekar25, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.

[danextian · Accepted solution]

Hi @ERing 

With the current structure of your data, you will need to have a lengthy measure. The measure below is just for the first two columns

Average of First 2 = 
VAR _tbl =
    FILTER ( 'Average Across Multiple Columns Sample Data', TRUE () )
VAR _analysis =
    SELECTCOLUMNS ( _tbl, "@value", [Analysis] )
VAR _budgeting =
    SELECTCOLUMNS ( _tbl, "@value", [Budgeting] )
VAR _combined =
    UNION ( _analysis, _budgeting )
RETURN
    AVERAGEX ( _combined, [@Value] )

If you want a  less lengthy code, you will need to re-shape your data. Please see @pankajnamekar25 's response.

[ERing]

Thanks @danextian  I don't mind the code being a little length however my only concern with the approach you outlined is that there is potential for new score columns to be added into the data at some point. If this happens, I would need to remember to update the measure to include the new score columns.

Is there any way to write the code so that it considers the Score_Category or either "Process" or "Skills" rather than the name of the Score column?

[danextian]

That will be the case, DAX cannnot dynamically add new columns and they don't have a grouping you can use to call in a calculation. You will have to explicitly specify them. 

[ajaybabuinturi]

Hi @ERing,
I am seeing there is NO data (Process and Skill) in Score_Category column. Is it expected or should we create a calculated column for Score_Category?

Thanks,

[ERing]

@ajaybabuinturi The Score Category column will have a value of either Process or Skills. Each Opportunity_ID has two rows. The first row has the scores for Process and the second row has the scores for Skill.

I'm not sure why the values in that column are not showing for you. Can you please try to refresh and see it it populates?

[pankajnamekar25]

Hello @ERing 

Unpivot Score Columns in Power Query

Select all the score columns (both Process and Skills).

Right-click → Unpivot Columns.

Rename

Attribute → Score_Type

Value → Score_Value

Now your table will look like

Representative | Opportunity_ID | Score_Category | Score_Type | Score_Value

Create the Measures

Avg_Process_Score =

AVERAGEX(

    FILTER(

        'UnpivotedTable',

        'UnpivotedTable'[Score_Category] = "Process"

    ),

    'UnpivotedTable'[Score_Value]

)

Measure for Skills Avg

Avg_Skills_Score =

AVERAGEX(

    FILTER(

        'UnpivotedTable',

        'UnpivotedTable'[Score_Category] = "Skills"

    ),

    'UnpivotedTable'[Score_Value]

)

Thanks

 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.

[ERing]

Thanks @pankajnamekar25 I've followed the steps you provided in the PBIX in the link below. The only issue is that the unpivoted data structure prevents me from creating the table visual with each Score_Type as a column as I have in my original post.

PBIX Unpivoted 

[Ashish_Mathur]

Hi,

You should create a matrix visual with Score_Type in the column section.