Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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:
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.
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.
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:
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 @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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
126 | |
78 | |
78 | |
60 | |
51 |
User | Count |
---|---|
164 | |
84 | |
68 | |
68 | |
58 |