Forum Discussion
Solved
Weighted Avg calculation
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 C...
Sudharsanan , Assuming all are columns
Divide(Sum(Table[CY sales]), Sumx(Table, Divide([PY sales],[PY qty]) * Table[Cyqty]) ) -1
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.
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
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
Sudharsanan
It depends where your year column is, if it is in the same table thenW AVG = DIVIDE( SUM(Table2[CySale]), SUMX( FILTER ( TABLE2 ,TABLE2[Year] = 2022 ) , DIVIDE( Table2[PYSale] , Table2[PY Qty] ) * Table2[CyQty] ) )-1Hi Fowmy
i am able to get the correct result for individual product but the overall value is still not coming up correctly.