Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi everyone,
I've been trying to create a Top 5 based on the screen shot you're seeing there.
What I want is the top 5 Sales People based from the volume (one graph) and total of applications (another graph)
+
I'd want a line showing the percentage of grand total for this top 5 (when I show value as percentage, it just gives me percentage of my top 5, not grand total)
+
Because I have a date associated to each row, do you know if I can create a relationship to a date hierarchy so it will dynamically update ?
Thanks a lot,
Nick,
Try this:
Measure = Calculate(SUM(table[volume]) ; Calculatetable( table ; TOPN( 5 ; table ; SUM(table[volume] ))
The measure works but on a graph it gives me all the sales people (not only top 5) even when I filter by less than 6,
Hi @NB3 ,
Based on my test, you could use the Top N filter for you data:
You could also download the pbix file to have a view.
Regards,
Daniel He
Sorry, i made a mistake with the measure 😞
Do you happen to have a dimension table which holds your sales persons or are theý only in the main table?
No they are only in the main table 😕
Measure = Calculate(SUM(table[volume]) ; TOPN( 5 ; SalespersonTable ; SUM(table[volume] )
For this to work there are a few steps:
Take your salesperson column and add it as a new query in query editor
Do a remove duplicates on that column
Add a relationship between salesperson in the main table and salesperson in the dim table
Then try the above measure
TopN isnt my strong suit, but i hope this works! 🙂
Can you paste the data into a table on this thread please?
@danno here you go, thanks
Submit Date | Application | Volume | Sales Person |
Feb 27, 2019 | Add | 20,000.00 | JJ |
Feb 21, 2019 | App | 12,000.00 | AP |
Jan 31, 2019 | App | 10,000.00 | AP |
Mar 12, 2019 | Add | 10,000.00 | NB |
Feb 22, 2019 | App | 200.00 | RC |
Jan 31, 2019 | Add | 3,000.00 | RC |
Jan 15, 2019 | Add | 4,000.00 | LG |
Jan 08, 2019 | Add | 8,500.00 | AP |
Jan 31, 2019 | Add | 54,000.00 | AP |
Jan 31, 2019 | App | 17,000.00 | JJ |
Jan 29, 2019 | App | 18,000.00 | JJ |
Jan 25, 2019 | App | 18.00 | JJ |
Jan 25, 2019 | Add | 35,000.00 | LA |
Feb 04, 2019 | Add | 90.00 | PO |
Feb 28, 2019 | Add | 8,500,000.00 | PO |
Mar 13, 2019 | App | 85,000.00 | ER |
Mar 22, 2019 | App | 12,000.00 | QW |
Jan 09, 2019 | App | 270.00 | AP |
Jan 31, 2019 | Add | 200,000.00 | LG |
Jan 03, 2019 | App | 300,000.00 | LG |
Jan 16, 2019 | App | 50,000.00 | RC |
Feb 28, 2019 | Add | 8,450,000.00 | JJ |
Feb 28, 2019 | App | 120,000.00 | JJ |
Mar 14, 2019 | App | 50,000.00 | JJ |
Feb 28, 2019 | Add | 15,000.00 | PO |
Feb 22, 2019 | App | 12,000.00 | PO |
Mar 26, 2019 | Add | 58,000.00 | DA |
Mar 06, 2019 | App | 150,000.00 | RE |
Jan 14, 2019 | App | 130,000.00 | DA |
Jan 24, 2019 | Add | 580,000.00 | DA |
Feb 27, 2019 | App | 155,000.00 | PO |
Mar 06, 2019 | Add | 4,816,118.00 | LG |
Feb 06, 2019 | Add | 150,000.00 | RC |
Feb 28, 2019 | App | 36,000.00 | NB |
Feb 28, 2019 | App | 1,510.00 | NB |
Feb 25, 2019 | App | 80,051.00 | NB |
Jan 18, 2019 | App | 16,105.00 | BF |
Jan 25, 2019 | App | 1,530.00 | BF |
Jan 31, 2019 | App | 1,602,588.00 | PM |
User | Count |
---|---|
98 | |
90 | |
78 | |
71 | |
64 |
User | Count |
---|---|
112 | |
96 | |
95 | |
67 | |
65 |