Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Sea_and_Anne
Helper I
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)

 

 

MachineAlarmsNature
A14a
A14s
A14f
A14g
B13a
B13g
D12r
E11t
F10h
G6w
H5q
I5a
J4g
K3h
L2t

 

 

 

Thanks in advance for your help

 

2 ACCEPTED SOLUTIONS
Vvelarde
Community Champion
Community Champion

hi @Sea_and_Anne

 

Use Summarize to group the Machines and sum the alarms

 

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




Lima - Peru

View solution in original post

Sean
Community Champion
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

 

2016-10-03 - RANK.png

 

 

Hope this also helps! Smiley Happy

View solution in original post

2 REPLIES 2
Sean
Community Champion
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

 

2016-10-03 - RANK.png

 

 

Hope this also helps! Smiley Happy

Vvelarde
Community Champion
Community Champion

hi @Sea_and_Anne

 

Use Summarize to group the Machines and sum the alarms

 

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




Lima - Peru

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

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

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

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.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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