Weighted average with several factors

[Mikaill]

Hello all,

I would like to create a weighted average based on a data set that is formatted as displayed below.

The end result should:

• Filter the Hours data by Name column (so it only takes into consideration values with the same name listed)
• Filter the Hours data by Month column (so it only takes into consideration values listed under the same month)
• Weight average of the Rating for each Rating Type across the Teams, with the abovementioned filters.

For example, if I have Person1 the result will be a set of data for that Person1 for each Month and each Rating Type, with average Rating weighted by amount of time (Hours) spent by that person on each Team they worked with during that Month.

Name	Month	Team	Hours	Rating Type	Rating
Person1	01/2024	Team1	20	Type1	2
Person1	01/2024	Team2	15	Type1	3
Person1	01/2024	Team1	20	Type2	3
Person1	01/2024	Team2	15	Type2	4
Person1	01/2024	Team1	20	Type3	3
Person1	01/2024	Team2	15	Type3	3
Person1	02/2024	Team1	15	Type1	3
...
Person2	01/2024	Team2	20	Type1	3
...

I got to admit, my power BI skills are layman at best, so I would appreciate a step-by-step solution being provided.

[v-yohua-msft]

Hi, @Mikaill  

Maybe you can try the following DAX, create a new column:

Weighted Average Rating = 
SUMX(
    FILTER(
        'Table1',
        'Table1'[Name] = EARLIER('Table1'[Name]) &&
        'Table1'[Month] = EARLIER('Table1'[Month])
    ),
    'Table1'[Rating] * 'Table1'[Hours]
)
/
SUMX(
    FILTER(
        'Table1',
        'Table1'[Name] = EARLIER('Table1'[Name]) &&
        'Table1'[Month] = EARLIER('Table1'[Month])
    ),
    'Table1'[Hours]
)

Here is my preview:

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data)

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Mikaill]

Hi, thanks for the response.

I don't think this satisfies my needs though, I believe this produces weighted average Rating when comparing between the Rating Types, while what I need it to get a weighted average within each Rating Type.

Some considerations:

• There is always 3 Rating Type categories, for each person (Name), each Month and each Team.
• The number of Teams can change, from 0 (that person did not work with any Team during specific Month) to several (without upper limit, but realistically no more than 5)
• I need a weighted average for each Rating Type separately, for each Name during each Month, weighted based on Hours spent on each Team
  
  

For example, as with the table before I provided a virtual data set, with Person1 having all Types of scores on multiple teams during a single month.
In this example I would like to be able to display weighted average for Person1 during 01/2024 for each of the Rating Types separately.
So a weighted average for their Rating Type1 during 01/2024, weighted based on the Hours spent by them on each Team (Team1 and Team2), and then the same for Rating Type2 and Type3.
The outcome with that data set would be something like:
Person1 01/2024 - Weighted average for Rating Type1 = (20/35*2 (Hours on Team1 vs total Hours during 01/2024 times their Type1 score on Team1) + 15/35*3 (Hours on Team2 vs total Hours during 01/2024 times their Type1 score on Team2))/2 (number of Teams they worked during the Month)
The same for Type2 and Type3.

Apologies if I am not being clear enough, the cross dependencies seem to be harder to convey through written form, I'll try to get a pbix dummy file uploaded later on.
For now here is an Excel sheet with expected outcome displayed - example.xlsx 

[v-yohua-msft]

Hi, @Mikaill 

I'm sorry I didn't understand what you meant.

You can create these Measures:

Hours1 = 
VAR _totalhours = SUMX(FILTER('Table','Table'[Rating Type]="Type1"),'Table'[Hours])
RETURN _totalhours

Tyoe1 w.avg = 
VAR _table = SUMMARIZE('Table','Table'[Name],'Table'[Month],'Table'[Team],"weight value", CALCULATE(SUM('Table'[Hours])*SUM('Table'[Rating]),FILTER('Table','Table'[Rating Type]="Type1")))
VAR _table1 = SUMMARIZE(_table,'Table'[Name],'Table'[Month],"w.avg",DIVIDE(DIVIDE(SUMX(_table,[weight value]),[Hours1]),COUNTAX(_table,[Month])))
RETURN MAXX(_table1,[w.avg])

Tyoe2 w.avg = 
VAR _table = SUMMARIZE('Table','Table'[Name],'Table'[Month],'Table'[Team],"weight value", CALCULATE(SUM('Table'[Hours])*SUM('Table'[Rating]),FILTER('Table','Table'[Rating Type]="Type2")))
VAR _table1 = SUMMARIZE(_table,'Table'[Name],'Table'[Month],"w.avg",DIVIDE(DIVIDE(SUMX(_table,[weight value]),[Hours1]),COUNTAX(_table,[Month])))
RETURN MAXX(_table1,[w.avg])

Tyoe3 w.avg = 
VAR _table = SUMMARIZE('Table','Table'[Name],'Table'[Month],'Table'[Team],"weight value", CALCULATE(SUM('Table'[Hours])*SUM('Table'[Rating]),FILTER('Table','Table'[Rating Type]="Type3")))
VAR _table1 = SUMMARIZE(_table,'Table'[Name],'Table'[Month],"w.avg",DIVIDE(DIVIDE(SUMX(_table,[weight value]),[Hours1]),COUNTAX(_table,[Month])))
RETURN MAXX(_table1,[w.avg])

Here is my preview:

If the above content is not what you want, you can also check out the following posts, which may be able to give you some ideas or solve your problem:

Solved: Weighted Average DAX - Microsoft Fabric Community

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Mikaill]

Hello @v-yohua-msft ,

Again, I very much so appreciate you taking the time to help me, however, I do not think this applies the weights at the level I need (unless I have mis-applied your solution).

If I see correctly, this returns a weighted average per Month, weighted across all months.
What I need is the average to be weighted within the same Month, but between different teams.
Please see below on the left my initial state (data plus non-weighted average for each team) vs the outcome I got with your proposition (the scores for each team during each month remain the same, but the monthly and the person's total average score changes, and to a value lower than expected based on the scores).

I'd appreciate if you could take a look into the Excel sheet I linked in a message above, to see the expected outcome.