Weighted Average - sum them on categories

[cosminc]

Hi all,

i have an issue with a measure wich contains weighted average

i made this measure:

Weighted AVG =

VAR S1=

DIVIDE (

SUMX ( source, source[Quantity] * source[Sales1] ),

SUMX ( source, source[Quantity] ),

0

)

VAR S2=

DIVIDE (

SUMX ( source, source[Quantity] * source[Sales2] ),

SUMX ( source, source[Quantity] ),

0

)

RETURN

DIVIDE ( S1, S2, 0 )

i need to use this in a graph with an hierarchy - Salesman/City/Region with Legend Year

all fine when is on Salesman dimensions

but when is put on City or Region the measures don't sum this measure for each Salesman for all Salesmen included in a City; it returns me an average between them.

so i need to make this average on each salesman, but also to sum these distinct averages when a use a category which includes salesmen

may anyone help me with this?

Thanks in advance,

Cosmin

below i put a source example:

Year	MonthNo	Year Month	Salesman	Quantity	City	Region	Sales1	Sales2
2019	2	201902	George	26	A	AAAA	59	236
2019	1	201901	George	9	A	AAAA	68	68
2019	1	201901	Alex	4	B	AAAA	59	118
2019	2	201902	Jenna	10	C	BBBB	93	465
2019	1	201901	Jenna	15	C	BBBB	63	126
2019	1	201901	Aimee	8	C	BBBB	59	177

[v-xicai · Accepted solution]

Hi cosminc,

According to your description, I create sample data to reproduce the scenario. Based on your original data, I add three rows data for further analysis.

Then, create measure Weighted AVG to meet your need.

Weighted AVG =

VAR S1=

DIVIDE (CALCULATE(SUM(source[Quantity])*SUM(source[Sales1])),CALCULATE(SUM(source[Quantity])),0)

VAR S2=

DIVIDE (CALCULATE(SUM(source[Quantity])*SUM(source[Sales2])),CALCULATE(SUM(source[Quantity])),0)

RETURN

DIVIDE ( S1, S2, 0 )

Choose table visual to display the result showing picture below.

 

Here is my test pbix file link: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EdcHWBoJeGtLqZ_sXU...

If above result doesn’t meet your requirement, please clarify your desired output.

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.