topic Re: Create an Average Column of corresponding Scores using DAX in DAX Commands and Tips

Create an Average Column of corresponding Scores using DAX

Anonymous — Sat, 14 May 2022 14:20:44 GMT

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 !!!

Re: Create an Average Column of corresponding Scores using DAX

tamerj1 — Sat, 14 May 2022 19:24:30 GMT

Anonymous

Can you providw sample data file?

Re: Create an Average Column of corresponding Scores using DAX

Anonymous — Sat, 14 May 2022 20:32:46 GMT

tamerj1

Below is the sample data:

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

Re: Create an Average Column of corresponding Scores using DAX

tamerj1 — Sun, 15 May 2022 20:42:22 GMT

Hi Anonymous

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

SUM ( TabaleNsme[ColumnName] )

Re: Create an Average Column of corresponding Scores using DAX

Anonymous — Tue, 17 May 2022 02:45:25 GMT

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.