How to calculate average in a new table referencing other tables

[wsspglobal]

Hi

I have a table that consists countries, geographic groupings, and their corresponding scores per country as shown below

I would like to calculate the average score of each Geographic Group in another table, so I created a table using the DISTINCT function to get a list of unique values from the "Geographic Group" column. How would you suggest I add a column and calculate the average score for each corresponding geographic group?

Thank you!

[Cmcmahan]

If you need this as a calculated table, you can use this to create it isntead of your current DISTINCT expression:

SUMMARIZECOLUMNS( 'Table'[Geographic Group], "Average", AVERAGE('Table'[Score]) )

The easier way to set this up, if your end goal is to display the 2nd table you have below as a table visual, is to add Geographic Group and Score to a table visual, and select the dropdown on Score to change it to an average instead of a sum.

[wsspglobal]

Hi @Cmcmahan 

This looks great, but I actually have more than one "Score" that I woud like to calculate. E.g. Score 1, Score 2, ... each have a column to calculate the average scores. How can I do that?

Thanks!

[Cmcmahan]

If you still haven't solved the issue, you would just add new calculated columns for each of your score calculations.  

SUMMARIZECOLUMNS( 
    'Table'[Geographic Group], 
    "Average", AVERAGE('Table'[Score]) 
    "Score2", SUM('Table'[Score2]),
    "Score3", [Some_measure])

[Anonymous]

@wsspglobal  - There's no need to create another table. What you want to do is add a Measure to your table like this:

Average Score = AVERAGE('YourTable'[Score])

Then, you can add Geographic Group and Average Score to a Table Visual or other visual.

I hope this helps. If it does, please Mark as a solution.
I also appreciate Kudos.

[wsspglobal]

Hi @Anonymous 

Thanks for the suggestion. But I have some further steps to take, so I would like to create a table instead of a visual. Can you suggest how to replicate what would be in the visual in the table view?

Thanks!