How to calculate average for sum of 1st max and 2 nd max value from last 3 preceding month value

[Ajith_Kumar]

Hi,

How to calculate the last 3 preceding month values and then calculate the average of the first 2 maximum values within those 3 preceding values.

Here my expected output.

Excepted output column comes from Actual Amount.


Thanks,
Ajith

[Anonymous · Accepted solution]

Hi @Ajith_Kumar ,

I created a sample pbix file(see the attachment), please find the details in it.

1. Add index column in Power Query Editor

= Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type)

2. Create two measures as below

Last 3 Preceeding value = 
VAR _selindex =
    SELECTEDVALUE ( 'Table'[Index] )
VAR _minindex =
    CALCULATE ( MIN ( 'Table'[Index] ), ALLSELECTED ( 'Table' ) )
RETURN
    IF (
        _selindex = _minindex,
        BLANK (),
        CALCULATE (
            SUM ( 'Table'[Actual Amount] ),
            FILTER (
                ALLSELECTED ( 'Table' ),
                'Table'[Index] >= _selindex - 3
                    && 'Table'[Index] < _selindex
            )
        )
    )

Average of the first 2 maximum values = 
VAR _selindex =
    SELECTEDVALUE ( 'Table'[Index] )
VAR _tab =
    SUMMARIZE (
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Index] >= _selindex - 3
                && 'Table'[Index] < _selindex
        ),
        'Table'[Index],
        "@ActualAmt", SUM ( 'Table'[Actual Amount] )
    )
VAR _tab2 =
    ADDCOLUMNS ( _tab, "@Rank", RANKX ( _tab, [@ActualAmt] ) )
VAR _maxvalue1 =
    SUMX ( FILTER ( _tab2, [@Rank] = 1 ), [@ActualAmt] )
VAR _maxvalue2 =
    SUMX ( FILTER ( _tab2, [@Rank] = 2 ), [@ActualAmt] )
RETURN
    ( _maxvalue1 + _maxvalue2 ) / 2

Best Regards