Skip to main content
cancel
Showing results for 
Search instead 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

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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