Forum Discussion
Comparison to minimum
Hi Everyone,
Apologies in advance if this is a duplicate response, I thought I had replied, but I do not see it.
I have tried the suggestions given, but did not have any luck. I am going to provide some more details on my data hoping it helps. I have about 200K rows, with each row being a dispatch. The dataset includes columns such as $$$ per trip, distance per trip, and hour of the day of the trip.
I've taken that data set and made a matrix in PBI which shows the total $$$, total distance, and the dollars per mile (dpm). See below the first six hours of data where the first four columns are calculated correctly in PBI. I have also shown two additional columns which I added for the purpose of the example but were calculated in Excel. These last two columns are the columns I want to add to matrix for my PBI report.
| hour in day | sum of $$$ | sum of distance (miles) | dpm | dpm min (DESIRED RESULT) | delta (DESIRED RESULT) |
| 0 | $ 150,468.34 | 31,069 | 4.8 | 4.2 | 0.6 |
| 1 | $ 109,293.39 | 23,020 | 4.7 | 4.2 | 0.5 |
| 2 | $ 78,192.71 | 16,879 | 4.6 | 4.2 | 0.4 |
| 3 | $ 58,451.66 | 12,850 | 4.5 | 4.2 | 0.3 |
| 4 | $ 48,309.60 | 11,237 | 4.3 | 4.2 | 0.1 |
| 5 | $ 48,203.37 | 11,357 | 4.2 | 4.2 | 0.0 |
| 6 | $ 82,614.76 | 18,477 | 4.5 | 4.2 | 0.2 |
When I used the suggestions given to me, instead of getting 4.2 for dpm (column 5), I was getting the lowest individual trip dpm ($$$/mile), but I want the lowest of the (sum of $$$/sum of distance) for the entire hour.
Hopefully this is clearer.
Appreciate the help.
Hey, @klew31
Depending on your requirement, you can try these measures:
dpm min =
MINX(ALL('Table'),[dpm])Delta =
SUMX('Table',[dpm]-[dpm min])
And you can get whatever you want, like this:
You can download my test pbix file here
Best regards
Qin Community Support _Robert Team
If this post helps,then consider Accepting it as the solution to help other members find it faster.