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
| 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 |
Sudharsanan , Assuming all are columns
Divide(Sum(Table[CY sales]), Sumx(Table, Divide([PY sales],[PY qty]) * Table[Cyqty]) ) -1
8 Replies
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
