Aggregation Problem regarding finding average for repeated values

[Anonymous]

Im facing an issue regarding aggregation and Im doing average of a value. I have below values  and Im trying to find the average value but Im not able to get the values correctly. Any help will be greatly appreciated. Here I need to define a measure that will calculate average length of stay ofcourse I need to have this measure at all granular levels. But when i take the sum goes wrong or Im not able to calculate at each level as well. Here the same values repeat for every business date. We need to pick only one Length of Stay for a reservation and then find out the average . Im looking for a solution that will work for individual level as well as when aggregated as well ie in the below scenario at a daily level and also at the hotel level in total.

[v-yuta-msft · Accepted solution]

@Anonymous ,

Create measures using dax below:

Average Length of Stay for Hotel = 
AVERAGEX (
    SUMMARIZE (
        'Table',
        'Table'[Reservation],
        "Length", CALCULATE (
            MAX ( 'Table'[Length of Stay] ),
            ALLEXCEPT ( 'Table', 'Table'[Reservation] )
        )
    ),
    [Length]
)

Average Length of Stay May be for Busniness Dates 04-Jan to 05-Jan = 
AVERAGEX (
    SUMMARIZE (
        FILTER (
            'Table',
            'Table'[Business Date]
                IN { DATEVALUE ( "04-jan-20" ), DATEVALUE ( "05-jan-20" ) }
        ),
        'Table'[Reservation],
        "Length", CALCULATE (
            MAX ( 'Table'[Length of Stay] ),
            ALLEXCEPT ( 'Table', 'Table'[Reservation] )
        )
    ),
    [Length]
)

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-yuta-msft 

Thanks a lot for your reply. Can we have a single measure or rather a generic measure that will work across all or both the scenarios that it will work for any granularity .
Here in the below example we are seeing a filter hardcoded as 04 and 5th Jan as well.

[amitchandak]

You have to try something like this. But before that, you have to make sure you unique reservation

CALCULATE(countx(SUMMARIZE(filter(CROSSJOIN(Table2,Dim_Date),Dim_Date[Calendar_Date] >= Table2[StayStartDate] && Dim_Date[Calendar_Date]<= Table2[StayEndDate]),Dim_Date[Calendar_Date]),Dim_Date[Calendar_Date]),CROSSFILTER(Dim_Date[Calendar_Date],Table2[StartDate],None))

In summarize , you can add reservation id as one group by along with date

[Anonymous]

@v-yuta-msft @amitchandak  thanks much for the reply. 

The calculated measure  in the below format seem to work in almost all scenarios.. Testing with more scenarios. Thanks for the help. The below dax works.

AVERAGEX(SUMMARIZE(RESERVATION_DAILY;RESERVATION_DAILY[RESV_ID];"LENGTH";CALCULATE (

MAX (RESERVATION_DAILY[LENGTH_OF_STAY]);

ALLEXCEPT(RESERVATION_DAILY;RESERVATION_DAILY[RESV_ID])));[LENGTH])

[Anonymous]

@v-yuta-msft 

Thanks very much for the solution regarding the above average calculation. However Im getting error when this is done for larger number of rows say for 1 year when i try to fetch the average for a Hotel . Im getting the error saying limitation of 1 million rows in Direct Query. I totally understand the scenario. But when i check in the database for the particular hotel for that year I dont have 1 million records, I only have 205237 records. Please advice.