How to Calculate Difference between max and min in one column based on each unique value of another

[Anonymous]

I am new to Power BI and I was trying to figure out how to calculate the difference between the min and max value for a column "Idle time" and "Idle fuel" for each invidual or unique date

So for an output I would want something similar to:

Date                                            Change in Idle time                Change in Idle Fuel

Thursday, July 19, 2018               0.25                                        1.00

Friday, July 20,2018                     0                                             0

Saturday July 21, 2018                2.4                                           8.13

...

I would also appreciate any explanation as to how/why the solution works as I am new to Power BI and I am trying to understand more about working in the environment as a whole.

Thanks,

Matt

[dedelman_clng · Accepted solution]

For your example, you are going to want to create a measure, so that it accepts the "filters" that are implicitly created when you put various columns in a visualization. Basically we will find the min and max values within the measure and calculate the delta. The visualization that you use to display the figures will apply a filter on date, and the measure will calculate the min and max for each of the dates

Change in Idle Time = 
  var __MinVal = MIN(Table[Idle time])
  var __MaxVal = MAX(Table[Idle time])

return
  __MaxVal - __MinVal


Change in Idle Fuel = 
  var __MinVal = MIN(Table[Idle fuel])
  var __MaxVal = MAX(Table[Idle fuel])

return
  __MaxVal - __MinVal

I would also recommend going through Microsoft's free guided learning for PowerBI if you are new to the platform. They cover all the basics including measures & calculated columns.

Hope this helps

David

[Anonymous]

Simple and great explanation @dedelman_clng.

[Anonymous]

Thank you for your help!

I was going down a much more convoluted path to try to get the same info (Taking unique values for a new table and then trying to find a "For Each" funciton to create the min an max for each value in the new table based on the values in the orignal table and calculate the delta from there).

Taking advantage of Power BI's built in features is definitely going to be a learning curve and I appreciate your help as I venture into it!