Average of a measure over FY with worst month removed

[Anonymous]

Hello all,

I have been thinking about this measure for a while and I am stumped.

I have a measure that I calculating a percentage of customers who meet a criteria over all months in the current context. 

What I want to do is take these values then remove the worst one in each fiscal year [FY] (april to april) and then average the remaining values. So if it was a FY that has finished this would be 11 values, for the current FY (April 2018 ->  August 2018) there has been 4 months completed so I would only be averaging 3 items.

I currently have a measure that will produce this result correctly when only one fiscal year is selected, however when more than one FY is selected it will only remove the worst performing month from all the years, not the worst from each FY. The definition for this measure is below.

Measure = 
AVERAGEX (
    CALCULATETABLE (
        TOPN (
            DISTINCTCOUNT ( DateTable[StartOfMonthDate] ) - 1,
            VALUES ( DateTable[StartOfMonthDate] ),
            [Yes Percent], DESC
        ),
        FILTER ( ALLSELECTED ( DateTable ), DateTable[Date] <> BLANK () )
    ),
    [Yes Percent]
)

The model itself is simple, I have a fact table, with one to many relationships between a date table and a customer dim table. The relationship between customer and the fact table is bidirectional

See link below for a pbix file with dummy data.

https://www.dropbox.com/s/8kqqrc1rmnspols/Measure%20Problem.pbix?dl=0

Current result

Month&Year	Measure
Apr-17	66.67%
May-17	77.78%
Jun-17	55.56%
Jul-17
Aug-17	80.00%
Sep-17	80.00%
Oct-17	80.00%
Nov-17	80.00%
Dec-17	70.00%
Jan-18	70.00%
Feb-18	80.00%
Mar-18	80.00%
Apr-18	80.00%
May-18	70.00%
Jun-18	60.00%
Jul-18	90.00%

Expected Result

Month&Year	Measure
Apr-17	66.67%
May-17	77.78%
Jun-17	55.56%
Jul-17
Aug-17	80.00%
Sep-17	80.00%
Oct-17	80.00%
Nov-17	80.00%
Dec-17	70.00%
Jan-18	70.00%
Feb-18	80.00%
Mar-18	80.00%
Apr-18	80.00%
May-18	70.00%
Jun-18
Jul-18	90.00%

As described earlier, the measure I currently have is only removing the lowest value, not the lowest value in each FY

Thank you for your help

[v-lili6-msft]

HI, @Anonymous

TOPN (
            DISTINCTCOUNT ( DateTable[StartOfMonthDate] ) - 1,
            VALUES ( DateTable[StartOfMonthDate] ),
            [Yes Percent], DESC
        )

this just remove the worst performing month from the table you selected,

you need to group data by fiscal year and then remove the worst performing month for each fiscal year.

Best Regards,

Lin

[Anonymous · Accepted solution]

I managed to solve the problem by creating a ranking measure that ranks within each fiscal year and then averagex over a virtual table removing any items with the lowest rank.