cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

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
2 ACCEPTED SOLUTIONS
Super User

Hi,

try this:

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

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

8 REPLIES 8
Super User

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

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.

Super User

Hi,

try this:

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

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.

Super User

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?

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
Super User

Yeah looks like it works for those values too:

What values are you getting?

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