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 

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's avatar
amitchandak
Super User
6 years ago

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'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.