TOP N

[mysho999]

Hi
i need advice.

I have a spreadsheet of data.

I would need to make two tables:

The first table will show the TOP 2 department by Duration
The second table will be the top1 Code and Code description within the top Department.

Example
Table 1: TOP2 departments (ENG / Maintenance)

Table 2: TOP 1 Code / Code description from ENG and TOP1 code / code description from Maintenance

Or if it was possible to do everything in one table, it would be fantastic.

DATA SOURCE: https://drive.google.com/drive/folders/1cQF3zhHtCLjaFDg7CLSyerMW1qtWbdz6?usp=sharing 

Well thank you

[amitchandak]

@mysho999 , For TOPN Refer

For Rank Refer these links

https://databear.com/power-bi-dax-topn-function/

https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...
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

[mysho999]

HI @Anonymous 

thanks a lot, I'll try today.

just a question ... Will the code work dynamically? What do I think ... Every day, a few hundred rows are added to the database. This means that there can be two different TOP departments every day.

Thank you

[Anonymous]

Hi @mysho999 ,

For the first table, it's dynamic.

For the second table, I hard-code it to calculate the top 1 for ENG and Maintenance department as you said.

If you want to change it, you need to change the label in the DAX.

Aiolos Zhao

[mysho999]

Hi @Anonymous 

i've tried your solution and I'm close enough to success. Could you still help?

1. I would need to add a filter by date (TOP 3 department as well as Code, code de ... will change based on time range)
2. Probably more complicated ... In table 2 I only need to show data based on the TOP 3 department. (for example: if in table 1 there is a top three x, y, z then in table 2 I need to visualize TOP1 only for x, y, z. If TOP 3 in the first table changes, TOP 1 in the second table will also change.)

Source: https://drive.google.com/drive/folders/1cQF3zhHtCLjaFDg7CLSyerMW1qtWbdz6?usp=sharing
Thank you 1000 times

[Anonymous]

Hi @mysho999 ,

I create 2 tables using DAX, hope that's what you want:

Table = TOPN(2,SUMMARIZE('Hárok1','Hárok1'[Deparment],"total duration",SUM('Hárok1'[Duration (min)])),[total duration],DESC)

Table 2 = UNION(TOPN(1,SUMMARIZE(FILTER('Hárok1','Hárok1'[Deparment] in {"ENG"}),'Hárok1'[Deparment],'Hárok1'[Code],'Hárok1'[Code description],"top code duration",SUM('Hárok1'[Duration (min)])),[top code duration],DESC),
                TOPN(1,SUMMARIZE(FILTER('Hárok1','Hárok1'[Deparment] in {"Maintenance"}),'Hárok1'[Deparment],'Hárok1'[Code],'Hárok1'[Code description],"top code duration",SUM('Hárok1'[Duration (min)])),[top code duration],DESC))

Please try.

Aiolos Zhao

[adityavighne]

@mysho999 

at department filter you will get Top N filter

so select Top N and By Value - Duration