Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Aggregation Problem regarding finding average for repeated values

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 gr...
Reports
  • v-yuta-msft's avatar
    v-yuta-msft
    6 years ago

    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's avatar
Anonymous
Not applicable
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

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.