Average of Average with filters

[Kritika_]

Hello,
I need to calculate the avg of avg.

I need to group TAT days by EMAILID and calculate the average for distinct email ids based on selected year from the filter.
Then I further need to calculate the average of the averages calculated.

I have used:

AVERAGEX(SUMMARIZE(Sheet1,Sheet1[EMAILID],Sheet1[TAT days]),Sheet1[TAT days]) as measure but it ignores the the group by calculates normal average.

In a column it is showing same values with selected yr for all the years in the table.

note:My dashboard doesn't use auto calendar,so i have written custom conditions for current yr ytd,quarter and previous yrs.So will have to write 3 of the same dax for different conditions.

Any suggestions on how to acheive this?

Thankyou so much for your time in advance!!!

[Anonymous · Accepted solution]

Hi @Kritika_,

I'd like to suggest you use SUMMARIZE function to fix the detail level records and correspond TAT days calculation result. Then you can do aggregate on them with less category fields to apply the first level aggregation.  

After these steps, you can use iterator function to apply the second level aggregation:

formula =
VAR summary =
    GROUPBY (
        SUMMARIZE (
            ALLSELECTED ( Sheet1 ),
            Sheet1[EMAILID],
            [Create Date],
            [Close Date],
            "TAT", [TAT days]
        ),
        [EMAILID],
        "Avg TAT", AVERAGEX ( CURRENTGROUP (), [TAT] )
    )
RETURN
    AVERAGEX ( summary, [Avg TAT] )

Regards,

Xiaoxin Sheng