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.

v-yuta-msft's avatar
v-yuta-msft
Community Support
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])