Forum Discussion

Advocate I

8 years ago

Solved

Average by minimum and maximum date by group

Hello community, I am trying to get an average per unique ID on the minimum and maximum date in a specified period by group. The table has columns which contains the groupname, ID, date and scor...

Need Help

Eric_Zhang
8 years ago
SNV

Based on my understanding, it seems that you're looking for an output as below.

If this is your case, you can reference below measure. See more details in the attached pbix file.

Measure 2 = VAR Tbl1 = SUMMARIZE ( FILTER ( yourTable, yourTable[date] = yourTable[max date] || yourTable[date] = yourTable[min date] ), yourTable[groupname], yourTable[id], "AVG_PER_ID_ON_MAX&&MIN_DATE", AVERAGE ( yourTable[score] ) ) RETURN AVERAGEX ( Tbl1, [AVG_PER_ID_ON_MAX&&MIN_DATE] )

For more specific suggestion, please post some sample data and expected output. Even better you can upload the pbix file to OneDrive or any web drive and share the download link. Do note to mask sensitive data before uploading.
avg.zip69 KB

Eric_Zhang

Microsoft Employee

8 years ago

SNV

Based on my understanding, it seems that you're looking for an output as below.

If this is your case, you can reference below measure. See more details in the attached pbix file.

Measure 2 =
VAR Tbl1 =
    SUMMARIZE (
        FILTER (
            yourTable,
            yourTable[date] = yourTable[max date]
                || yourTable[date] = yourTable[min date]
        ),
        yourTable[groupname],
        yourTable[id],
        "AVG_PER_ID_ON_MAX&&MIN_DATE", AVERAGE ( yourTable[score] )
    )
RETURN
    AVERAGEX ( Tbl1, [AVG_PER_ID_ON_MAX&&MIN_DATE] )

For more specific suggestion, please post some sample data and expected output. Even better you can upload the pbix file to OneDrive or any web drive and share the download link. Do note to mask sensitive data before uploading.

avg.zip69 KB