Reply
Frequent Visitor
Posts: 2
Registered: ‎01-17-2019
Accepted Solution

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

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

 

data.PNG

 

 

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


Accepted Solutions
Highlighted
New Contributor
Posts: 450
Registered: ‎08-25-2016

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

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

View solution in original post


All Replies
Highlighted
New Contributor
Posts: 450
Registered: ‎08-25-2016

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

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

Frequent Visitor
Posts: 2
Registered: ‎01-17-2019

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

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!

Super User
Posts: 668
Registered: ‎11-01-2017

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

[ Edited ]

Simple and great explanation @dedelman_clng.