Table with TOP N category

[94veiga]

I have a table like this

IdEMP	Profession	Income
EMP0001	Chef	200
EMP0001	Waiter	100
EMP0001	Sous-Chef	50
EMP0002	Sous-Chef	300
EMP0002	Waiter	100

I want to get a table that shows me the profession in which they get the most of their income. It should be like this.

IdEMP	Profession
EMP0001	Chef
EMP0002	Sous-Chef

Thank you very much for your support.

Pedro V.

[v-yingjl · Accepted solution]

Hi @94veiga ,

You can create a measure like this, put it in the visual filter and set its value as 1:

Rank = 
CALCULATE (
    RANKX (
        FILTER ( ALL ( 'Table' ), 'Table'[IdEMP] IN DISTINCT ( 'Table'[IdEMP] ) ),
        'Table'[Income],
        SUM ( 'Table'[Income] ),
        DESC,
        DENSE
    )
)

Attached a sample file in the below, hopes it could help.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@94veiga , assuming income is measure, create a rank like

rankx(filter(allselected(table[Profession], Table[EMP] ), table[Profession] = max(table[Profession])),[Income],,desc,)

and use a visual level filter rank =1

[94veiga]

Idk why but the result is a number. 

Categ = rankx(filter(allselected(LiquidacionesDetalle[CatFact], LiquidacionesDetalle[idautor] ), LiquidacionesDetalle[idautor] = max(LiquidacionesDetalle[CatFact])),'Detalle medidas'[Total Detalle BI],1,desc)

Any mistake in here?

[amitchandak]

@94veiga , if it is a column then you need to have a column rank like

'Detalle medidas'[Total Detalle BI]

Categ = rankx(filter(LiquidacionesDetalle, LiquidacionesDetalle[idautor] = earlier(LiquidacionesDetalle[CatFact])),'Detalle medidas'[Total Detalle BI],,desc)

can you share data with original column names

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...