Comparison to minimum

[Anonymous]

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.

[v-robertq-msft]

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.

[Anonymous]

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.

[Ashish_Mathur]

Hi,

Does this measure work?

=MINX(ALL(Data[hours in day]),[dpm])

[v-robertq-msft]

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.

[amitchandak]

@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.

[CNENFRNL]

Hi, @Anonymous , you might want to try this measure,

Overall Min = CALCULATE ( MIN ( Data[Dollars per mile] ), ALL ( Data ) )