Measure to Calculate Age Range Based on Group

[apmulhearn]

Hello,

I have a table of guests travelling on various trips. Each trip has a unique code. A small sample is depicted below.

On Departure Code DGCRD:20221004LEO, there are 4 guests travelling, ages 68 - 77. I want to be able to calculate the range of ages - in this example, 9 years.

Additionally, I do not want to include blank/null values,

so DGCHRT:20221221LEO would be 53, as there are several guests with undisclosed ages.

Sample table:

Departure Code (Departure) (Departure)Depart AgeFirst Payment Date (Booking) (Booking)

DGCGRD:20220906LEO
DGCGRD:20220906LEO
DGCGRD:20221004LEO	72	8/26/2020
DGCGRD:20221004LEO	77	8/26/2020
DGCGRD:20221004LEO	77	8/27/2020
DGCGRD:20221004LEO	68	8/27/2020
DGCGEA:20231029LEO
DGCBIN:20220122LEO	72	12/22/2021
DGCGRD:20220906LEO
DGCBIN:20220122LEO	74	12/22/2021
DGCBEA:20221221-1LEO	56	1/16/2020
DGCBEA:20221221-1LEO	29	1/16/2020
DGCBEA:20221221-1LEO	28	1/16/2020
DGCBEA:20221221-1LEO	28	1/16/2020
DGCBEA:20221221-1LEO	24	1/16/2020
DGCBEA:20221221-1LEO	25	1/16/2020
DGCBEA:20221221-1LEO	57	1/16/2020
DGCPHM:20220804LEO	51	4/16/2021
DGCHRT:20221221LEO	73	12/22/2021
DGCHRT:20221221LEO	20	12/22/2021
DGCHRT:20221221LEO		12/22/2021
DGCHRT:20221221LEO		12/22/2021
DGCHRT:20221221LEO		12/22/2021
DGCTZS:20220807LEO	16	12/10/2019
DGCTZS:20220807LEO	16	12/10/2019
DGCTZS:20220807LEO	46	12/10/2019
DGCTZS:20220807LEO	47	12/10/2019
DGCPHM:20221222LEO	36	12/23/2021
DGCPHM:20221222LEO	38	12/23/2021
DGCPHM:20230803LEO	50	4/16/2021
DGCPHM:20230803LEO	52	4/16/2021
DGCPHM:20230803LEO	15	4/16/2021
DGCPHM:20230803LEO	17	4/16/2021

[smpa01]

@apmulhearn  use this as a measure

Measure = CALCULATE(MAX('Table'[Age]),ALLEXCEPT('Table','Table'[Departure Code]))-CALCULATE(MIN('Table'[Age]),ALLEXCEPT('Table','Table'[Departure Code]))

[apmulhearn]

Hi - thank you. This calculation is working well, but I don't think I explained my use case. I want to be able to compare the number of bookings where the range is greater than or equal to a certain number (say, 18) to the number of bookings as a whole. Can you help me here? 

[smpa01 · Accepted solution]

@apmulhearn  try this

Measure =
VAR _filt =
    FILTER (
        'Table',
        CALCULATE (
            COUNT ( 'Table'[Departure Code] ),
            ALLEXCEPT ( 'Table', 'Table'[Departure Code] )
        ) > 18
    )
VAR _partition =
    ALLEXCEPT ( 'Table', 'Table'[Departure Code] )
RETURN
    CALCULATE ( MAX ( 'Table'[Age] ), _partition, _filt )
        - CALCULATE ( MIN ( 'Table'[Age] ), _partition, _filt )

[smpa01]

@apmulhearn  did you try this out yet?

[apmulhearn]

Hello and sorry for the delay, I've been trying to use what you gave me and solve my problem, but I'm failing. When I use the measure in a chart, the below is what I receive. The first value is from the first Measure you suggested; the second value is from the second measure you suggested. 

I'm sure I'm messing this up but I'm not sure how.  What I want is to show, by year in the chart, a) how many bookings (identifiable by field 'Departure Code')  have an age range of 18 or more years, b) how many total bookings there are, and c) a as a percent of b.