Average of Top 5 Measures in row (matrix table)

[A_a_a]

Hi All,

I have a Matrix Table with Places, Weeks and percentage values (measure).

I need to calculate an average of top 5 measures in one row. So for example place 1: weeks 1 to 5, but place 3: weeks 1,2,3,4,6.

Is there any way to to that? I was trying different options but no results.

Just to highlight that percentage values are measures, which I calculated earlier.

Thanks,

G.

[v-easonf-msft]

Hi， @A_a_a 

You may need to create a summary table to get the records of Top5 measure value in each Place.

Then you can calculate the average based on this summary table.

Best Regards,
Community Support Team _ Eason

[A_a_a]

Thank you.

I am wondering if I can create a summary table based on two columns and one measure?

New Table = SUMMARIZE(ActualData,ActualData[place],ActualData[Week],VALUE(ActualData[%]))

[%] - it is a measure. It is not working with it...

Any idea?

Thanks,

G.

[A_a_a]

Ok, it worked:

New Table = SUMMARIZE(ActualData,ActualData[place],ActualData[Week],"Measure",VALUE([%]))

[A_a_a]

Ok, but now I am strugling with the correct TopN, first of all I need top 5...

I started with:

Top by place and week =

RANKX(ALLSELECTED('New Table'[place],'New Table'[Week]),[Measure 2],,DESC,Dense)

and the output is not ideal...

I need such output

and not sure how to calculate average top 5 by place.

Please help.

[v-easonf-msft · Accepted solution]

Hi, @A_a_a 

Try calculated table like:

Top5 = 
GENERATE (
    VALUES ( 'ActualData'[Place] ),
    TOPN (
        5,
        FILTER (
            SELECTCOLUMNS (
                ALL ( 'ActualData'[Place], 'ActualData'[Week] ),
                "v_Place", 'ActualData'[Place],
                "v_week", 'ActualData'[Week],
                "v_percentage", [%]
            ),
            [v_Place] = [Place]
        ),
        [v_percentage], DESC
    )
)

measure:

Average = CALCULATE(AVERAGE(Top5[v_percentage]),ALLEXCEPT(Top5,Top5[v_Place]))

Best Regards,
Community Support Team _ Eason

[A_a_a]

Perfect, thank you !!!