Forum Discussion
Comparison to minimum
Hi, Anonymous
According to your requirement, you can try these measures:
Overall min dollar per mile =
MINX(ALL('Table'),[Dollars per mile])Delta =
SUMX('Table',[Dollars per mile]-[Overall min dollar per mile])
And you can get what you want, like this:
You can download my test pbix file here
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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.
Hi,
Does this measure work?
=MINX(ALL(Data[hours in day]),[dpm])
