cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

Average of Top 3 and Bottom 3 Values

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.

2 ACCEPTED SOLUTIONS
Super User

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

Proud to be a Super User!

Frequent Visitor

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)
12 REPLIES 12
Super User

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

Proud to be a Super User!

Frequent Visitor

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!

Super User

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

Proud to be a Super User!

Frequent Visitor

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

Super User

what are the types? H and QH?

Proud to be a Super User!

Frequent Visitor

Super User

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

Proud to be a Super User!

Frequent Visitor
 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.

Super User

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

Proud to be a Super User!

Frequent Visitor

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

Super User

pls see the attachment below

Proud to be a Super User!

Frequent Visitor

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)

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors