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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Mikaill
Regular Visitor

Weighted average with several factors

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.

 

NameMonthTeamHoursRating TypeRating
Person101/2024Team120Type12
Person101/2024Team215Type13
Person101/2024Team120Type2

3

Person101/2024Team215Type2

4

Person101/2024Team120Type3

3

Person101/2024Team215Type3

3

Person102/2024Team115Type1

3

...    

 

Person201/2024Team220Type1

3

...    

 

 

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

4 REPLIES 4
v-yohua-msft
Community Support
Community Support

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:

 

vyohuamsft_1-1713333138655.png

 

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:

  • 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:

vyohuamsft_0-1713426998411.png

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).
Screenshot 2024-04-18 111500.png

 

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors