Forum Discussion
TOP N
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
6 Replies
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
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
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
Hi Anonymous
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
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/367415
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
