Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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!
Solved! Go to 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.
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.
@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
Please accept as a solution if its worked.
@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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
125 | |
78 | |
76 | |
59 | |
51 |
User | Count |
---|---|
166 | |
84 | |
68 | |
67 | |
57 |