Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
83 | |
70 | |
65 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |