Weighted Average Price calculation - with multiple filters

[haoqizha]

Hi,

I have got below tables:

Client	Product	Sales	QTY	AUP	Month Year
A	ABC	$100	50	$2	Aug 2022
B	ABC	$240	60	$4	Aug 2022
A	ABC	$500	5	$10	July 2022
B	ABC	$300	10	$3	July 2022

*AUP is Average Unit Price: Sales/QTY

So what I am trying to get is a Weighted Average of AUP within a certain Month Year for a given Product:

In Aug, the Weighted Average price for product ABC = (100/340) *2 + (240/340)*4

In July, the Weighted Average price for product ABC = (500/800) * 10 + (300/800)*3

Not too sure how to get this through DAX Measure?

Thanks!

[v-jianboli-msft · Accepted solution]

Hi @haoqizha ,

Approve @ddpl . Here is another measure, please try:

Measure = DIVIDE(SUMX('Table',[Sales]*[AUP]),SUMX('Table',[Sales]))

Output:

If you want to apply all the columns in the table visual, please try:

Measure 2 = 
var _a = FILTER(ALL('Table'),[Month Year]=MAX('Table'[Month Year]))
return DIVIDE(SUMX(_a,[Sales]*[AUP]),SUMX(_a,[Sales]))

Output:

Best Regards,

Jianbo Li

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

[ddpl]

@haoqizha First create calculated column "Weighted Average" as per below

Weighted Average = 
         var _A = CALCULATE(SUM('Table (2)'[Sales]),FILTER('Table (2)','Table (2)'[Month Year] = EARLIER('Table (2)'[Month Year])))
         var _B =DIVIDE('Table (2)'[Sales],_A)*'Table (2)'[AUP]
         return
             _B

then create visual table as per below

Please accept as a solution if its worked.

[haoqizha]

@ddpl  Thanks for your reply.  Can I ask why do you use "EARLIER" function here?

[ddpl]

@haoqizha Earlier fuction we have used for grouping of the MontYear value so that we will get the total for A and B ie A = 340 and B =800