Video: Top 3 Salespeople per Region

Content Ideas
1

Hi,

Module: Business Analytical Series
Video: Find Your Top 3 Salespeople Per Region Automatically in Power BI using DAX

I don’t understand why it’s necessary to bring back the context (VAR RankingContext) in measure ‘Top 3 Salespeople per Region’.
I can see that the results don’t work without the VAR (same number repeated) but I don’t understand the logic.
Would be great if you could further explain. Thank you.

Regards,
Kevin

2

Welcome to the Enterprise DNA Forum @Kevin. We’re happy to have you in the community. To get started, I highly suggest you check these guides that Sam created for us to build a more collaborative environment. https://forum.enterprisedna.co/t/how-to-use-the-enterprise-dna-support-forum/3951

3

@Kevin,

Welcome to the forum! We are glad to have you here.

To help understand this, let’s look first at the correct answer with the Ranking Context included:

image

Now let’s comment out the ranking context line:

Top 3 Salespeople per Region = 
VAR
	RankingContext = VALUES( Salespeople[Salesperson Name] )
RETURN
CALCULATE( [Total Sales],
	TOPN( 3,
		ALL( Salespeople[Salesperson Name] ),
		[Total Sales] )
   // ,RankingContext
)

What this measure now says is “calculate the total sales for the top three salespeople, based on total sales and remove the filter (via ALL) on salesperson name, so that it does this calculation across all salespeople within a region”. By removing the ranking context, you leave the ALL statement in place and active, and so the value that will be returned is the same $27,676 calculated above, but with the filter on salesperson name removed, this value will be returned for every row within a region, and that’s exactly what happens:

image

Note that the total still changes by region, since there’s nothing that removes that filter context.

I hope this helps clarify things. I would definitely recommend going through the sections of this course that address evaluation, filter, and row context. Those are the essential concepts at play here.

  • Brian
3 Likes
4

Thanks Brian! The explanation really helped. I understand it now! :smiley:

5

Nice one Brian