TOP10 - how group by...

[Sea_and_Anne]

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

Thanks in advance for your help

[Vvelarde · Accepted solution]

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]) ))

[Sean · Accepted solution]

@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!

[Vvelarde · Accepted solution]

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]) ))