Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Create an Average Column of corresponding Scores using DAX

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:

sha009_1-1652537182180.png

 

Expected Output Table:

sha009_2-1652537418705.png

 

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

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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 :

Ailsamsft_0-1652755412070.png

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.

Ailsamsft_1-1652755412072.png

You will get a result like this :

Ailsamsft_2-1652755412074.png

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

Ailsamsft_3-1652755412075.png

The final result is as shown :

Ailsamsft_4-1652755412076.png

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.

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

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 :

Ailsamsft_0-1652755412070.png

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.

Ailsamsft_1-1652755412072.png

You will get a result like this :

Ailsamsft_2-1652755412074.png

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

Ailsamsft_3-1652755412075.png

The final result is as shown :

Ailsamsft_4-1652755412076.png

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
Super User
Super User

@Anonymous 

Can you providw sample data file?

Anonymous
Not applicable

@tamerj1 

Below is the sample data:

sha009_0-1652560318271.png

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

 

Hi @Anonymous 

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

SUM ( TabaleNsme[ColumnName] )

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.