Weighted Avg calculation

[Sudharsanan]

Hi friends,

I need help in calculating weighted avg for the below data using DAX. can someone help me?

Logic: (Sum of CY sales)/((sum of PY sales)/(sum of PY qty))*sum of Cyqty)))-1 

Cust	prod	CySale	CyQty	PYSale	PY Qty
1	A	1,193	15	526	7
2	B	1,165	3	777	2
3	C	1,028	4	596	3
4	A	802	5	1,389	10
5	B	794	6	922	8
6	C	767	4	171	1
7	A	717	8	4,303	48
8	B	492	1	1,350	3
9	C	452	8	193	4
10	A	445	5	229	3
11	B	408	5	1,183	16
12	C	376	3	242	2
13	A	355	2	1,420	8
14	B	295	3	270	3
15	C	235	1	571	3
16	A	215	10	1,022	50
17	B	163	2	1,311	18
18	C	119	5	590	31
19	A	78	5	41	3
20	B	63	3	41	2
21	C	61	1	102	2
22	A	41	1	154	5
23	B	29	1	51	2

[Fowmy]

@Sudharsanan 

Can you reconfirm your logic?, I tred it on a worksheet and got 36.63%

[Sudharsanan]

Hi @Fowmy 

Hope this helps..

avg Price = (pysale/pyqty)*cyqty.

i am able to get the anwer correct for individual line but as a overall i am not getting it.

[Fowmy]

@Sudharsanan 
The measure provided by @amitchandak  should work,

W AVG = 

DIVIDE(
    SUM(Table2[CySale]),
    SUMX(
        TABLE2,
        DIVIDE( Table2[PYSale] , Table2[PY Qty] ) * Table2[CyQty] 
    )
)-1
 

[Sudharsanan]

Thank you @Fowmy it works.

can you also guide me in using the filter under Sum X please? i need to filter the year in  some cases

[Fowmy]

@Sudharsanan 

It depends where your year column is, if it is in the same table then

W AVG = 

DIVIDE(
    SUM(Table2[CySale]),
    SUMX(
        FILTER ( TABLE2 ,TABLE2[Year] = 2022 ) ,
        DIVIDE( Table2[PYSale] , Table2[PY Qty] ) * Table2[CyQty] 
    )
)-1

[Sudharsanan]

Hi @Fowmy 

i am able to get the correct result for individual product but the overall value is still not coming up correctly.

[amitchandak · Accepted solution]

@Sudharsanan , Assuming all are columns

Divide(Sum(Table[CY sales]), Sumx(Table, Divide([PY sales],[PY qty]) * Table[Cyqty]) ) -1

[Sudharsanan]

Thank you @amitchandak 

Are you able to get the answer 10.45%?