Top 5 + Percentage of grand total + Date Hierarchy dynamic

[NB3]

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,

[tex628]

Try this: 

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

[NB3]

@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,

[tex628]

Do you mind taking a picture of the graph?

[NB3]

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

Thansk,

[v-danhe-msft]

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

[tex628]

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?

[NB3]

No they are only in the main table 😕 

[tex628]

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

[NB3]

@tex628 

Same thing as before happened 😞

But thanks, that was worth a try 🙂

[danno]

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

[NB3]

@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