Average of Top 3 and Bottom 3 Values

[MortimerMcFly]

Hey @all,

I have a problem I was trying to solve for a couple of time now. 

What am I trying to do. What do I need? 

I want to create a visual (line chart) which shows the average of the top 3 values and the bottom 3 values over a period of time. 

Thank you for your help.

[ryan_mayu · Accepted solution]

@MortimerMcFly 

you can try this

Measure =

VAR _top=TOPN(3,'Table','Table'[value],DESC)

VAR _bottom=TOPN(3,'Table','Table'[value],ASC)

return (sumx(_top,[value])+sumx(_bottom,[value]))/6

[ryan_mayu · Accepted solution]

@MortimerMcFly 

you can try this

Measure =

VAR _top=TOPN(3,'Table','Table'[value],DESC)

VAR _bottom=TOPN(3,'Table','Table'[value],ASC)

return (sumx(_top,[value])+sumx(_bottom,[value]))/6

[MortimerMcFly]

It did the trick, thank you! 
Do you know hot include a filter statement? I tried this: Solved: How to include a filter in a topN dax function? - Microsoft Fabric Community but it is not working in my case. Cheers!

[ryan_mayu]

then pls update the sample data and expected output. Let me have a try.

[MortimerMcFly]

I need the Top 3 and Bottom 3 solution but with an additional filter so i get the average for different types. 

13.01.2024 14:45	QH	74,7
13.01.2024 08:15	QH	92,03
13.01.2024 17:30	H	90,03
13.01.2024 23:30	H	60,39
13.01.2024 07:45	QH	98,33
13.01.2024 09:15	QH	88,11
13.01.2024 16:30	QH	83,8
13.01.2024 16:45	QH	88,19
13.01.2024 22:15	H	71,03
13.01.2024 18:30	QH	81,87
13.01.2024 18:00	H	94,11
13.01.2024 06:45	QH	78,13
13.01.2024 22:00	QH	94,29
13.01.2024 23:45	QH	52,86
13.01.2024 09:45	H	87,17
13.01.2024 18:15	QH	88,82
13.01.2024 23:15	QH	73,41
13.01.2024 10:00	H	90,09

[ryan_mayu]

what are the types? H and QH?

[MortimerMcFly]

Yes. Sorry I posted it under your other answer. Missclicked.. 

[ryan_mayu]

could you pls paste the data here?(not the screenshot). And what's the expected output?

[MortimerMcFly]

13.01.2024 14:45		QH		74,7
13.01.2024 08:15		QH		92,03
13.01.2024 17:30		H		90,03
13.01.2024 23:30		H		60,39
13.01.2024 07:45		QH		98,33
13.01.2024 09:15		QH		88,11
13.01.2024 16:30		QH		83,8
13.01.2024 16:45		QH		88,19
13.01.2024 22:15		H		71,03
13.01.2024 18:30		QH		81,87
13.01.2024 18:00		H		94,11
13.01.2024 06:45		QH		78,13
13.01.2024 22:00		QH		94,29
13.01.2024 23:45		QH		52,86
13.01.2024 09:45		H		87,17
13.01.2024 18:15		QH		88,82
13.01.2024 23:15		QH		73,41
13.01.2024 10:00		H		90,09

I need the Top 3 and Bottom 3 solution but with an additional filter so i get the average for different types. 

[ryan_mayu]

i think the previous solution can work on this scenario as well.

[MortimerMcFly]

I dont get the expected result in a matrix visual..

[ryan_mayu]

pls see the attachment below

[MortimerMcFly · Accepted solution]

A third Variable did the trick: 

C_D12 =

VAR Table_ID1 = Filter('Table1','Table1'[market]="D_12")

VAR _top3 = TOPN(3,Table_ID1,'Table1'[price],DESC)

VAR _bot3 = TOPN(3,Table_ID1,'Table1'[price],ASC)

RETURN

    DIVIDE(SUMX(_top3,'Table1'[price])+SUMX(_bot3,'Table1'[price]),6)