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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
cosminc
Post Partisan
Post Partisan

Weighted Average - sum them on categories

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:

YearMonthNoYear MonthSalesmanQuantityCityRegionSales1Sales2
20192201902George26AAAAA59236
20191201901George9AAAAA6868
20191201901Alex4BAAAA59118
20192201902Jenna10CBBBB93465
20191201901Jenna15CBBBB63126
20191201901Aimee8CBBBB59177

 

1 ACCEPTED SOLUTION
v-xicai
Community Support
Community Support

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.

 

2.jpg

 

 

 

 

 

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.

 

3.png 

 

 

 

 

 

 

 

 

 

 

 

 

 

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.

View solution in original post

1 REPLY 1
v-xicai
Community Support
Community Support

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.

 

2.jpg

 

 

 

 

 

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.

 

3.png 

 

 

 

 

 

 

 

 

 

 

 

 

 

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.