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

Helper I

## TOP10 - how group by...

Hello all,

I have one table with the Machine name , the number of Alarms and the alarm nature. I want to create a simple graph with the top 10 of the machines with the hightest number of alarms(don't mind the alarm nature).
So, i have created the top 10 table.

But don't give me the necessary output, the machine A have 4 registers so takes 4 places..... How can I write the TOPN function in order to appear machine A only one time and not fill the first 4 levels of the top 10.

`NewTable = TOPN(10,'Table','Table'[Alarm],DESC)`

 Machine Alarms Nature A 14 a A 14 s A 14 f A 14 g B 13 a B 13 g D 12 r E 11 t F 10 h G 6 w H 5 q I 5 a J 4 g K 3 h L 2 t

2 ACCEPTED SOLUTIONS
Community Champion

Use Summarize to group the Machines and sum the alarms

NewTable = TOPN(10,SUMMARIZE(SampleTable,SampleTable[Machine],"TotalAlarms",SUM(SampleTable[Alarms]) ))

Lima - Peru
Community Champion

@Sea_and_Anne an alternative would be to RANK the machines by the SUM of alarms

1) Create Total Alarms MEASURE

`Total Alarms = SUM ('Table'[Alarms])`

2) Create the Ranking MEASURE

```Machine RANK =
IF (
HASONEVALUE ( 'Table'[Machine] ),
RANKX ( ALL ( 'Table'[Machine] ), [Total Alarms] )
)```

NOTE => if you want to combine steps 1 and 2 you HAVE TO wrap the SUM in CALCULATE like this!

```Machine RANK 2 =
IF (
HASONEVALUE ( 'Table'[Machine] ),
RANKX ( ALL ( 'Table'[Machine] ), CALCULATE ( SUM ( 'Table'[Alarms] ) ) )
)```

3) Go to the Visual Level Filter => select Machine RANK => Show items when the value: is less than or equal to 10

Hope this also helps!

2 REPLIES 2
Community Champion

@Sea_and_Anne an alternative would be to RANK the machines by the SUM of alarms

1) Create Total Alarms MEASURE

`Total Alarms = SUM ('Table'[Alarms])`

2) Create the Ranking MEASURE

```Machine RANK =
IF (
HASONEVALUE ( 'Table'[Machine] ),
RANKX ( ALL ( 'Table'[Machine] ), [Total Alarms] )
)```

NOTE => if you want to combine steps 1 and 2 you HAVE TO wrap the SUM in CALCULATE like this!

```Machine RANK 2 =
IF (
HASONEVALUE ( 'Table'[Machine] ),
RANKX ( ALL ( 'Table'[Machine] ), CALCULATE ( SUM ( 'Table'[Alarms] ) ) )
)```

3) Go to the Visual Level Filter => select Machine RANK => Show items when the value: is less than or equal to 10

Hope this also helps!

Community Champion

Use Summarize to group the Machines and sum the alarms

NewTable = TOPN(10,SUMMARIZE(SampleTable,SampleTable[Machine],"TotalAlarms",SUM(SampleTable[Alarms]) ))

Lima - Peru

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 - June 2024

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

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors