Forum Discussion
Comparison to minimum
Hello Everyone,
I have a data table that I use that has the revenue dollars per mile by hour in the day. What I have done so far is calculated the revenue per mile in each hour of the day and I want to compare it to the lowest value calculated. See table below,
| Hour in Day | Dollars per mile | Overall min dollar per mile (all the same) | Delta |
| 0 | 4 | 2 | 2 |
| 1 | 2 | 2 | 0 |
| 2 | 6 | 2 | 4 |
I can make a card that shows the right number for the overall minimum, but once I put it into the matrix I get an error.
Any help would be appreciated.
6 Replies
Hi, Anonymous , you might want to try this measure,
Overall Min = CALCULATE ( MIN ( Data[Dollars per mile] ), ALL ( Data ) )Anonymous ,
As new columns
Overall Min = min(Table[Dollars per mile])
diff = [Dollars per] -[Overall Min]As new measures
Overall Min = CALCULATE ( min(Table[Dollars per mile]), ALLSELECTED (Table) )
Dollars per Sum = sum[Dollars per]diff = [Dollars per Sum] -[Overall Min]
Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.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.
