Forum Discussion
Weighted average with several factors
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
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.
Hello Anonymous ,
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.