Weighted Average at Row Level in Power BI Matrix Visual

[milonsarker]

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

[Anonymous · Accepted solution]

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.

[Anonymous]

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.

[milonsarker]

I've added the sample data. Also, original post already has the equation for weighted average stipend. 

[danextian]

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?

[milonsarker]

I've added the sample data. Also, original post already has the equation for weighted average stipend.