Solved: Calculate difference between slicer selected valu... - Page 2

AlexisGonzalez · ‎04-06-2020

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 minutes interval by days ( in this case day 5 minus day 1, per 15 min interval)

for 12:30:00 am 1.41 - 1.46

for 12:15:00 am 1.39 - 1.63

for 12:00:00 am 1.44 - 1.46

thanks..

Icey · ‎04-12-2020

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 - MinDayValue

Measure = 
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.

View solution in original post