Forum Discussion
Total by category
Dear Friend,
i have five client and calculated their Sell Qty,Avg 1 Avg2 group by date( using DAX).
Then i calculate Profit by formula: Sell Qty *(Avg1-Avg2). The value i am getting individual for each company are correct but when i see as total profit is different. If i will add all individual company one by one then Profit: -622058(which is correct). But Profit i am getting is: -1703848 as final value . how can i get -622058.
i solved it by measures:
Profit =sumx(values(sheet1[companyid]), [Qty_A]*([Avg_A]-[Avg_B]))
3 Replies
Dear Sir,
I tried with calculated column but the calculated values are not correct.
here are data:
company_id value Qty Qty type Date 100 125 1 A 02/05/2023 101 126 5 A 02/05/2023 102 127 6 A 02/05/2023 103 128 9 A 02/05/2023 104 129 10 A 02/05/2023 105 130 25 A 02/05/2023 106 115 36 A 02/05/2023 107 116 25 A 02/05/2023 108 117 22 A 02/05/2023 109 118 11 A 02/05/2023 110 119 14 A 02/05/2023 100 221 22 B 02/05/2023 101 222 55 B 02/05/2023 102 223 11 B 02/05/2023 103 224 47 B 02/05/2023 104 225 59 B 02/05/2023 105 226 65 B 02/05/2023 106 227 36 B 02/05/2023 107 239 25 B 02/05/2023 108 255 98 B 02/05/2023 109 266 12 B 02/05/2023 110 288 55 B 02/05/2023 100 250 3 A 03/05/2023 101 252 15 A 03/05/2023 102 254 18 A 03/05/2023 103 256 27 A 03/05/2023 104 258 30 A 03/05/2023 105 260 75 A 03/05/2023 106 230 108 A 03/05/2023 107 232 75 A 03/05/2023 108 234 66 A 03/05/2023 109 236 33 A 03/05/2023 110 238 42 A 03/05/2023 100 442 66 B 03/05/2023 101 444 165 B 03/05/2023 102 446 33 B 03/05/2023 103 448 141 B 03/05/2023 104 450 177 B 03/05/2023 105 452 195 B 03/05/2023 106 454 108 B 03/05/2023 107 478 75 B 03/05/2023 108 510 294 B 03/05/2023 109 532 36 B 03/05/2023 110 576 165 B 03/05/2023 and i used the measures:
1.
Qty_A = CALCULATE(SUM(Sheet1[Qty]),Sheet1[Qty type]="A")2.Qty_B = CALCULATE(SUM(Sheet1[Qty]),Sheet1[Qty type]="B")3.Value_A = CALCULATE(SUM(Sheet1[value]),Sheet1[Qty type]="A")4.Value_B = CALCULATE(SUM(Sheet1[value]),Sheet1[Qty type]="B")5.Avg_A = [Value_A]/[Qty_A]6.Avg_B = [Value_B]/[Qty_B]and finally formula for Profit is:Profit = [Qty_A]*([Avg_A]-[Avg_B])whenever i displayed in table, the individual Profits are correct but final profit (=1396.24) is wrong:on excel calculation, the profit (=569.3903😞
company_id Qty_A Avg_A Avg_B Profit 100 4 93.75 7.534091 344.8636 101 20 18.9 3.027273 317.4545 102 24 15.875 15.20455 16.09091 103 36 10.66667 3.574468 255.3191 104 40 9.675 2.860169 272.5932 105 100 3.9 2.607692 129.2308 106 144 2.395833 4.729167 -336 107 100 3.48 7.17 -369 108 88 3.988636 1.951531 179.2653 109 44 8.045455 16.625 -377.5 110 56 6.375 3.927273 137.0727 Final Profit 569.3903 could you please help me.
amitchandak Anonymous
i solved it by measures:
Profit =sumx(values(sheet1[companyid]), [Qty_A]*([Avg_A]-[Avg_B]))
