Forum Discussion

Frequent Visitor

3 years ago

Solved

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 individua...

jyaul12
3 years ago
i solved it by measures:
Profit =sumx(values(sheet1[companyid]), [Qty_A]*([Avg_A]-[Avg_B]))

Frequent Visitor

3 years ago

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

Frequent Visitor

3 years ago

i solved it by measures:

Profit =sumx(values(sheet1[companyid]), [Qty_A]*([Avg_A]-[Avg_B]))