Divide the Sum of Two Columns by Sum of another Two columns

[RoadeoAnalytics]

Hi,

Wondering if someone can help with an express that will divide sum of two columuns by a sum of another two columns. I'm trying to derive at an average rate for two quantities. In the table below I would like to sum Revenue 1 and Revenue 2 columns and divide it by sum of Quantity 1 and Quantity 2 columns. In this case it would be $54/63=$0.86.

I've tried the divide function but can't seem to get a correct answer.

Quantity 1	Quantity 2	Revenue 1	Revenue 2
3	9	$1.00	$7.00
5	6	$3.00	$5.00
6	3	$5.00	$4.00
4	7	$6.00	$6.00
3	9	$3.00	$4.00
6	2	$7.00	$3.00

[DOLEARY85 · Accepted solution]

Hi,

try this:

Measure = CALCULATE(SUM('Table'[Revenue 1])+SUM('Table'[Revenue 2]))/CALCULATE(SUM('Table'[Quantity 1])+SUM('Table'[Quantity 2]))

[DOLEARY85 · Accepted solution]

That's really odd, are your amount fields formatted as decimal?

i'm getting the correct answer using your data:

[RoadeoAnalytics]

Looks like the issue was that I've created a new Column. Now that I've tried this with a Measure, it gave me the correct answer of $2.25. There goes half a day I spent trying to figure this out. 

Thank you for all of your help with this, helped me figure out what I was doing wrong. Really appreciate it.

[DOLEARY85 · Accepted solution]

Hi,

try this:

Measure = CALCULATE(SUM('Table'[Revenue 1])+SUM('Table'[Revenue 2]))/CALCULATE(SUM('Table'[Quantity 1])+SUM('Table'[Quantity 2]))

[RoadeoAnalytics]

Thank you for your reply. This calculation produces an incorrect answer and what I've gotten before. What it does is that it calcuates the rates for each of the columns individually and then takes the average of the two rates, which isn't correct.

[DOLEARY85]

strange i appear to be getting the 0.86 that you needed:

do you have a copy of the PBI file i could take a look at?

[RoadeoAnalytics]

It works out becuase in my table the values are too close together so the answer for both will be almost identical. Can you please try with values below? The correct answer should be $.47. 

Quantity 1	Quantity 2	Revenue 1	Revenue 2
3	45	$1.00	$7.00
5	6	$3.00	$5.00
6	3	$5.00	$4.00
4	23	$6.00	$6.00
3	9	$3.00	$4.00
6	2	$7.00	$3.00
27	88	$25.00	$29.00

[DOLEARY85]

Yeah looks like it works for those values too:

What values are you getting?

[RoadeoAnalytics]

Hmm. Not really sure what's going on. The fomula you provided makes sense and should work. My real data looks like below. I can't share the PBX because it has client data in there.

For below the DL goes with DL and Ecom goes with Ecom. Trying to get a $ rate per stop.

The answer should be $2.25 but with the formula I'm getting $2.39. Can't really figure out why it's doing that.

DL STOPS	ECOM DL STOPS	DL STOPS $AMT	ECOM STOPS $AMT
40	616	107.2	1293.6
380	887	1,018.40	1862.7
301	640	806.68	1344
303	865	812.04	1816.5
339	906	908.52	1902.6
330	865	884.4	1816.5
37	590	99.16	1239
357	853	956.76	1791.3
304	809	814.72	1698.9
302	879	809.36	1845.9
352	879	943.36	1845.9
317	804	849.56	1688.4
44	534	117.92	1121.4
392	933	1,050.56	1959.3
311	641	833.48	1346.1
268	873	718.24	1833.3
313	825	838.84	1732.5
303	805	812.04	1690.5