March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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 |
Solved! Go to Solution.
Hi,
try this:
That's really odd, are your amount fields formatted as decimal?
i'm getting the correct answer using your data:
That's really odd, are your amount fields formatted as decimal?
i'm getting the correct answer using your data:
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.
Hi,
try this:
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:
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 |
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 |
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
90 | |
89 | |
85 | |
73 | |
49 |
User | Count |
---|---|
167 | |
148 | |
92 | |
72 | |
58 |