cancel
Showing results for
Did you mean:

## 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
Resident Rockstar

Hi,

try this:

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

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

8 REPLIES 8
Resident Rockstar

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.

Resident Rockstar

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.

Resident Rockstar

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
Resident Rockstar

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

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors