Create an Average Column of corresponding Scores using DAX

[Anonymous]

Hi All,

I want to have a calculated column (column H) such that Sum of Values from Column H corresponding to each unique WS Id(Column A) must be divided by count of that Unique WS Id(Column A).

Given Data Table:

Expected Output Table:

Example: In this for WS Id WS 887180323, value in H2 is (B2 value 56+ C2 value 80+ D2 value 81.6+ E2 value 60+ F2 value 80)/Count of Corresponding WS Id(A2 in this case 5)=71.52 ....And so on...

Thanks a lot !!!

[v-yetao1-msft · Accepted solution]

Hi @Anonymous 

Create a measure like this :

Measure =
VAR _SUM=CALCULATE(SUM('Table'[Commercial])+SUM('Table'[Delivery])+SUM('Table'[Price])+SUM('Table'[Quality])+SUM('Table'[Sustainability & Risk Management])+SUM('Table'[Technology]),ALLEXCEPT('Table','Table'[WS Id]))
VAR _COUNTROWS=CALCULATE(COUNT('Table'[WS Id]),ALLEXCEPT('Table','Table'[WS Id]))
RETURN DIVIDE(_SUM,_COUNTROWS)

Then you will get a result like this :

For each of your WS Id, there is only one data per column, so you can choose to set all numeric fields in the table to sum.

You will get a result like this :

If you don’t want to show the Total , you can turn it off in Totals .

The final result is as shown :

I have attached my pbix file , you can refer to it .

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

[tamerj1]

@Anonymous 

Can you providw sample data file?

[Anonymous]

@tamerj1 

Below is the sample data:

Sorry, can't attach pbix file as the option of attaching file is not available to me.

[tamerj1]

Hi @Anonymous 

Use a table visual and add the 7 columns as measures:

SUM ( TabaleNsme[ColumnName] )