average of Top n percent per month

[bengisby]

Goal: average seconds of only the lowest 80% of seconds

Based on data:

ID	yy-mm	Name	seconds
1	21-03	Ben	30
2	21-03	Joe	40
3	21-03	Ben	10
4	21-03	Joe	20
5	21-03	Joe	30
6	21-04	Joe	40
7	21-04	Joe	30
8	21-04	Ben	240
9	21-04	Ben	12
10	21-04	Ben	15

Desired Output:

Table Visualization:

yy-mm	top 80% Average
21-03	22.5
21-04	24.25

Note: 21-03 is ignoring ID 2 and 21-04 is ignoring ID 8 because they are not in the lowest 80%.

Ideally, the measure could work under the name column as well. E.g.

Name	Top 80% Average
Ben	16.75
Joe	30

[v-jingzhang · Accepted solution]

Hi @bengisby 

Please use the following measures.

top 80% Average by yy-mm =
VAR _yymm = SELECTEDVALUE ( 'Table 1'[yy-mm] )
VAR _80number = COUNTROWS ( FILTER ( ALL ( 'Table 1' ), 'Table 1'[yy-mm] = _yymm ) ) * 0.8
VAR _80table =
    FILTER (
        'Table 1',
        RANKX (
            FILTER ( ALL ( 'Table 1' ), 'Table 1'[yy-mm] = _yymm ),
            CALCULATE ( SUM ( 'Table 1'[seconds] ) ),
            ,
            ASC
        ) <= _80number
    )
RETURN
    AVERAGEX ( _80table, 'Table 1'[seconds] )

top 80% Average by name =
VAR _name = SELECTEDVALUE ( 'Table 1'[Name] )
VAR _80number = COUNTROWS ( FILTER ( ALL ( 'Table 1' ), 'Table 1'[Name] = _name ) ) * 0.8
VAR _80table =
    FILTER (
        'Table 1',
        RANKX (
            FILTER ( ALL ( 'Table 1' ), 'Table 1'[Name] = _name ),
            CALCULATE ( SUM ( 'Table 1'[seconds] ) ),
            ,
            ASC
        ) <= _80number
    )
RETURN
    AVERAGEX ( _80table, 'Table 1'[seconds] )

top 80% Average =
IF (
    ISFILTERED ( 'Table 1'[yy-mm] ),
    [top 80% Average by yy-mm],
    IF ( ISFILTERED ( 'Table 1'[Name] ), [top 80% Average by name] )
)

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[v-jingzhang · Accepted solution]

Hi @bengisby 

Please use the following measures.

top 80% Average by yy-mm =
VAR _yymm = SELECTEDVALUE ( 'Table 1'[yy-mm] )
VAR _80number = COUNTROWS ( FILTER ( ALL ( 'Table 1' ), 'Table 1'[yy-mm] = _yymm ) ) * 0.8
VAR _80table =
    FILTER (
        'Table 1',
        RANKX (
            FILTER ( ALL ( 'Table 1' ), 'Table 1'[yy-mm] = _yymm ),
            CALCULATE ( SUM ( 'Table 1'[seconds] ) ),
            ,
            ASC
        ) <= _80number
    )
RETURN
    AVERAGEX ( _80table, 'Table 1'[seconds] )

top 80% Average by name =
VAR _name = SELECTEDVALUE ( 'Table 1'[Name] )
VAR _80number = COUNTROWS ( FILTER ( ALL ( 'Table 1' ), 'Table 1'[Name] = _name ) ) * 0.8
VAR _80table =
    FILTER (
        'Table 1',
        RANKX (
            FILTER ( ALL ( 'Table 1' ), 'Table 1'[Name] = _name ),
            CALCULATE ( SUM ( 'Table 1'[seconds] ) ),
            ,
            ASC
        ) <= _80number
    )
RETURN
    AVERAGEX ( _80table, 'Table 1'[seconds] )

top 80% Average =
IF (
    ISFILTERED ( 'Table 1'[yy-mm] ),
    [top 80% Average by yy-mm],
    IF ( ISFILTERED ( 'Table 1'[Name] ), [top 80% Average by name] )
)

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.