Forum Discussion

Sea_and_Anne's avatar
Sea_and_Anne
Icon for Helper I rankHelper I
9 years ago
Solved

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

 

Need Help
  • Vvelarde's avatar
    Vvelarde
    9 years ago

    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's avatar
    Sean
    9 years ago

    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! :smileyhappy:

2 Replies

  • Vvelarde's avatar
    Vvelarde
    Icon for Community Champion rankCommunity Champion
    9 years ago

    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's avatar
    Sean
    Icon for Community Champion rankCommunity Champion
    9 years ago

    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! :smileyhappy: