Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RoadeoAnalytics
Advocate I
Advocate I

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 1Quantity 2Revenue 1Revenue 2
39$1.00$7.00
56$3.00$5.00
63$5.00$4.00
47$6.00$6.00
39$3.00$4.00
62$7.00$3.00
2 ACCEPTED SOLUTIONS
DOLEARY85
Super User
Super User

Hi,

 

try this:

 

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

View solution in original post

DOLEARY85
Super User
Super User

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

 

i'm getting the correct answer using your data:

 

DOLEARY85_0-1680468305706.png

 

View solution in original post

8 REPLIES 8
DOLEARY85
Super User
Super User

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

 

i'm getting the correct answer using your data:

 

DOLEARY85_0-1680468305706.png

 

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

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

 

DOLEARY85_0-1680463094294.png

 

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 1Quantity 2Revenue 1Revenue 2
345$1.00$7.00
56$3.00$5.00
63$5.00$4.00
423$6.00$6.00
39$3.00$4.00
62$7.00$3.00
2788$25.00$29.00

Yeah looks like it works for those values too:

 

DOLEARY85_0-1680466329195.png

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 STOPSECOM DL STOPSDL STOPS $AMTECOM STOPS $AMT
40616107.21293.6
3808871,018.401862.7
301640806.681344
303865812.041816.5
339906908.521902.6
330865884.41816.5
3759099.161239
357853956.761791.3
304809814.721698.9
302879809.361845.9
352879943.361845.9
317804849.561688.4
44534117.921121.4
3929331,050.561959.3
311641833.481346.1
268873718.241833.3
313825838.841732.5
303805812.041690.5

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.