Forum Discussion
Weighted Average at Row Level in Power BI Matrix Visual
Trying to develop a Power BI matrix visual which will show raw data in row level. I was able to show row level data by adding the Ranked column to the rows section of visual. Then in the total section, I need a custom calculation. Weighted Average Stipend is needed.
The formula is :
Weighted Stipend Average = SUM(Stipend * Number of Assistant)/SUM(Number of Assistant)
Here,
Stipend means various stipend like Teaching, Research, Graduate etc
Number of Assistant means number of teaching assistant, research assistant, graduate assistant etc
I tried using measure but I couldn't add measure in row level for every sub-category. Here is the table structure:
Here is the visual set up in Power BI:
Sample Data:
| DATA_TYPE_LABEL | CIP_6 | Ranked | Teaching_Stipend | Teaching_Num | Research_Stipend | Research_Num |
| Doctorate Adjusted Ranked Salaries | '01.0000 | 1 | 54103 | 33 | 50511 | 241 |
| Doctorate Adjusted Ranked Salaries | '01.0000 | 2 | 43147 | 28 | 43422 | 31 |
| Doctorate Adjusted Ranked Salaries | '01.0000 | 3 | 42420 | 8 | 43015 | 39 |
| Doctorate Adjusted Ranked Salaries | '01.0000 | 4 | 42342 | 2 | 40583 | 219 |
| Doctorate Adjusted Ranked Salaries | '01.0000 | 5 | 41881 | 5 | 34346 | 94 |
| Doctorate Adjusted Ranked Salaries | '01.0000 | 6 | 35486 | 4 | 33660 | 35 |
| Doctorate Adjusted Ranked Salaries | '01.0000 | 7 | 34885 | 33 | 33079 | 51 |
| Doctorate Adjusted Ranked Salaries | '01.0000 | 8 | 34323 | 2 | 33041 | 96 |
| Doctorate Adjusted Ranked Salaries | '01.0000 | 9 | 33008 | 6 | 32460 | 100 |
| Doctorate Adjusted Ranked Salaries | '01.0000 | 10 | 31741 | 8 | 32313 | 107 |
| Doctorate Adjusted Ranked Salaries | '01.0000 | 11 | 31060 | 31 | 31472 | 41 |
| Doctorate Adjusted Ranked Salaries | '01.0000 | 12 | 30871 | 9 | 31293 | 241 |
| Doctorate Adjusted Ranked Salaries | '01.0000 | 13 | 29555 | 8 | 31285 | 165 |
| Doctorate Adjusted Ranked Salaries | '01.0000 | 14 | 28613 | 9 | 29147 | 69 |
| Doctorate Adjusted Ranked Salaries | '01.0000 | 15 | 28553 | 4 | 28127 | 49 |
| Doctorate Adjusted Ranked Salaries | '01.0000 | 16 | 28315 | 14 | 27709 | 8 |
| Doctorate Adjusted Ranked Salaries | '01.0000 | 17 | 26891 | 4 | 27377 | 110 |
| Doctorate Adjusted Ranked Salaries | '01.0000 | 18 | 24903 | 9 | 26426 | 4 |
| Doctorate Adjusted Ranked Salaries | '01.0000 | 19 | 24714 | 7 | 26294 | 59 |
| Doctorate Adjusted Ranked Salaries | '01.0000 | 20 | 22208 | 3 | 25954 | 13 |
| Doctorate Adjusted Ranked Salaries | '01.0000 | 21 | 21023 | 7 | 24877 | 141 |
| Doctorate Adjusted Ranked Salaries | '01.0000 | 22 | 17589 | 1 | 23993 | 3 |
| Doctorate Adjusted Ranked Salaries | '01.0000 | 23 | 16411 | 8 | 23441 | 49 |
| Doctorate Adjusted Ranked Salaries | '01.0000 | 24 | 16190 | 15 | 21970 | 66 |
| Doctorate Adjusted Ranked Salaries | '01.0000 | 25 | 20500 | 62 | ||
| Doctorate Adjusted Ranked Salaries | '01.0000 | 26 | 20454 | 27 | ||
| Doctorate Adjusted Ranked Salaries | '01.0000 | 27 | 17053 | 66 | ||
| Doctorate Adjusted Ranked Salaries | '01.0000 | 28 | 16722 | 24 | ||
| Expected Summarised Value | 34093.85271 | 258 | 32666.45882 | 2210 |
Hi, milonsarker
You can try the following methods.
Average of Teaching_Stipend = DIVIDE(SUMX('Table',[Teaching_Stipend]*[Teaching_Num]),SUM('Table'[Teaching_Num]))Average of Research_Stipend = DIVIDE(SUMX('Table',[Research_Stipend]*[Research_Num]),SUM('Table'[Research_Num]))Is this the result you expected?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
- danextian
Super User
Hi milonsarker
Please post a workable sample data (not an image) representing your use case and your expected result from that. For example, in your screeshot, what values do you expect?
I've added the sample data. Also, original post already has the equation for weighted average stipend.
Hi, milonsarker
Can you provide example data or sample files? And what you expect the output to be. There is sensitive data that can be removed in advance. How to provide sample data in the Power BI Forum - Microsoft Fabric Community
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I've added the sample data. Also, original post already has the equation for weighted average stipend.
Hi, milonsarker
You can try the following methods.
Average of Teaching_Stipend = DIVIDE(SUMX('Table',[Teaching_Stipend]*[Teaching_Num]),SUM('Table'[Teaching_Num]))Average of Research_Stipend = DIVIDE(SUMX('Table',[Research_Stipend]*[Research_Num]),SUM('Table'[Research_Num]))Is this the result you expected?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
