cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Top 5 + Percentage of grand total + Date Hierarchy dynamic

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,

 

Capture_1.PNG

11 REPLIES 11
Highlighted
Resolver IV
Resolver IV

Re: Top 5 + Percentage of grand total + Date Hierarchy dynamic

Can you paste the data into a table on this thread please? 

Highlighted
Helper II
Helper II

Re: Top 5 + Percentage of grand total + Date Hierarchy dynamic

@danno  here you go, thanks

 

Submit DateApplicationVolumeSales Person
Feb 27, 2019Add20,000.00JJ
Feb 21, 2019App12,000.00AP
Jan 31, 2019App10,000.00AP
Mar 12, 2019Add10,000.00NB
Feb 22, 2019App200.00RC
Jan 31, 2019Add3,000.00RC
Jan 15, 2019Add4,000.00LG
Jan 08, 2019Add8,500.00AP
Jan 31, 2019Add54,000.00AP
Jan 31, 2019App17,000.00JJ
Jan 29, 2019App18,000.00JJ
Jan 25, 2019App18.00JJ
Jan 25, 2019Add35,000.00LA
Feb 04, 2019Add90.00PO
Feb 28, 2019Add8,500,000.00PO
Mar 13, 2019App85,000.00ER
Mar 22, 2019App12,000.00QW
Jan 09, 2019App270.00AP
Jan 31, 2019Add200,000.00LG
Jan 03, 2019App300,000.00LG
Jan 16, 2019App50,000.00RC
Feb 28, 2019Add8,450,000.00JJ
Feb 28, 2019App120,000.00JJ
Mar 14, 2019App50,000.00JJ
Feb 28, 2019Add15,000.00PO
Feb 22, 2019App12,000.00PO
Mar 26, 2019Add58,000.00DA
Mar 06, 2019App150,000.00RE
Jan 14, 2019App130,000.00DA
Jan 24, 2019Add580,000.00DA
Feb 27, 2019App155,000.00PO
Mar 06, 2019Add4,816,118.00LG
Feb 06, 2019Add150,000.00RC
Feb 28, 2019App36,000.00NB
Feb 28, 2019App1,510.00NB
Feb 25, 2019App80,051.00NB
Jan 18, 2019App16,105.00BF
Jan 25, 2019App1,530.00BF
Jan 31, 2019App1,602,588.00PM
Highlighted
Super User II
Super User II

Re: Top 5 + Percentage of grand total + Date Hierarchy dynamic

Try this: 

Measure =
Calculate(SUM(table[volume]) ; Calculatetable( table ; TOPN( 5 ; table ; SUM(table[volume] ))

Connect on LinkedIn
Highlighted
Helper II
Helper II

Re: Top 5 + Percentage of grand total + Date Hierarchy dynamic

@tex628 

 

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,

 

 

Highlighted
Super User II
Super User II

Re: Top 5 + Percentage of grand total + Date Hierarchy dynamic

Do you mind taking a picture of the graph?


Connect on LinkedIn
Highlighted
Helper II
Helper II

Re: Top 5 + Percentage of grand total + Date Hierarchy dynamic

@tex628  I'd need the graph to stop at DA while the % stays the same.

 

Thansk,

 

Capture_2.PNG

Highlighted
Super User II
Super User II

Re: Top 5 + Percentage of grand total + Date Hierarchy dynamic

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?


Connect on LinkedIn
Highlighted
Helper II
Helper II

Re: Top 5 + Percentage of grand total + Date Hierarchy dynamic

No they are only in the main table 😕 

Highlighted
Super User II
Super User II

Re: Top 5 + Percentage of grand total + Date Hierarchy dynamic

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! 🙂


Connect on LinkedIn

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors