Forum Discussion
AlexisGonzalez
6 years agoFrequent Visitor
Calculate difference between slicer selected values based on 15 min intervals
in this case, I have selected 3 days ( march 5 - 2 and 1) on the slicer, and I would like to have a measure in a new column with the subtraction of travel time column between the max and min 15 minut...
- 6 years ago
Hi AlexisGonzalez ,
Please check the attached PBIX file.
1. Enter Data.
Measure Table:
2. Create "Legend Table".
Legend Table = UNION(VALUES(Data[Day]),'Measure Table')3. Create measures.
Average of Travel Time between Selected Days = AVERAGE(Data[Travel Time (min)])Difference of Travel Time between Selected Days = VAR MaxDay = MAXX ( Data, Data[Day] ) VAR MinDay = MINX ( Data, Data[Day] ) VAR MaxDayValue = CALCULATE ( MAX ( Data[Travel Time (min)] ), FILTER ( Data, Data[Day] = MaxDay ) ) VAR MinDayValue = CALCULATE ( MAX ( Data[Travel Time (min)] ), FILTER ( Data, Data[Day] = MinDay ) ) RETURN MaxDayValue - MinDayValueMeasure = IF ( MAX ( 'Legend Table'[Day] ) = "Average of Travel Time between Selected Days", [Average of Travel Time between Selected Days], IF ( MAX ( 'Legend Table'[Day] ) = "Difference of Travel Time between Selected Days", [Difference of Travel Time between Selected Days], IF ( VALUE ( MAX ( 'Legend Table'[Day] ) ) IN VALUES ( Data[Day] ), CALCULATE ( AVERAGE ( Data[Travel Time (min)] ), FILTER ( Data, Data[Day] = VALUE ( MAX ( 'Legend Table'[Day] ) ) ) ) ) ) )4. Create visuals.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Icey
6 years agoCommunity Support
Hi AlexisGonzalez ,
Please check the attached PBIX file.
1. Enter Data.
Measure Table:
2. Create "Legend Table".
Legend Table = UNION(VALUES(Data[Day]),'Measure Table')
3. Create measures.
Average of Travel Time between Selected Days = AVERAGE(Data[Travel Time (min)])Difference of Travel Time between Selected Days =
VAR MaxDay =
MAXX ( Data, Data[Day] )
VAR MinDay =
MINX ( Data, Data[Day] )
VAR MaxDayValue =
CALCULATE (
MAX ( Data[Travel Time (min)] ),
FILTER ( Data, Data[Day] = MaxDay )
)
VAR MinDayValue =
CALCULATE (
MAX ( Data[Travel Time (min)] ),
FILTER ( Data, Data[Day] = MinDay )
)
RETURN
MaxDayValue - MinDayValueMeasure =
IF (
MAX ( 'Legend Table'[Day] ) = "Average of Travel Time between Selected Days",
[Average of Travel Time between Selected Days],
IF (
MAX ( 'Legend Table'[Day] ) = "Difference of Travel Time between Selected Days",
[Difference of Travel Time between Selected Days],
IF (
VALUE ( MAX ( 'Legend Table'[Day] ) ) IN VALUES ( Data[Day] ),
CALCULATE (
AVERAGE ( Data[Travel Time (min)] ),
FILTER ( Data, Data[Day] = VALUE ( MAX ( 'Legend Table'[Day] ) ) )
)
)
)
)
4. Create visuals.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
AlexisGonzalez
6 years agoFrequent Visitor
Thanks. this works perfectly...!!!!