Top/Bottom for multiple filter selections

DAX DAX Calculations
1

Hi All,

I have Region,Name,Sales,Profit columns.I need Top/Bottom Selection filter as 5,10,15
I am using Region and TOP/Bottom as filters.
If i select Region ,Top and Top 5 values my report should show top 5 sales based on region and customers .
Same way for Bottom also.

Thanks,

2

I you can add images of what you have and where you are currently at that always helps.

What you need I believe is covered here

And also somewhat in this example also

3

Thanks Sam for reply .Please check attached screenshot or below image for reference
I created top and bottom selections for 5,10,15 …are working good.But i have region filter also as filter selection (I need if i select East and Top 5 then results provide based on selection,in similar way for bottom also) with out region filters logic working good.

Sample%20Screenshot
Sample Screenshot.PNG731×468 10 KB

Thanks.

4

Are you saying your region filter is not working? If so it is likely to do with the relationships you have.

This calc should just adjust automatically due to the context changing (if your model has the correct relationships.

What formula are you using?

5

Region,customers and sales information are coming from one table only.Only top bottom I created new table and written variable concept from your videos.
My concern is How to apply Top and Bottom filter for multiple columns.
Formulas below:

Ranking Select = IF( HASONEVALUE( 'Ranking Selections'[Ranking] ), VALUES( 'Ranking Selections'[Rank Number] ), 10000 )

Top Sales by Rank = 

VAR
	RankingDimension = VALUES( Orders[Customer Name] )
VAR
	RankingSelect = [Ranking Select]
RETURN
CALCULATE( [Total sales],
	FILTER( RankingDimension,
		RANKX( ALL( Orders[Customer Name] ), [Total sales], , DESC ) <= RankingSelect ) )


Bottom Sales by Rank = 
VAR
	RankingDimension = VALUES( Orders[Customer Name] )
VAR
	RankingSelect = [Ranking Select]
RETURN
CALCULATE( [Total sales],
	FILTER( RankingDimension,
		RANKX( ALL( Orders[Customer Name] ), [Total sales], , ASC ) <= RankingSelect ) )


Selection = 
SWITCH( TRUE(),
	VALUES( 'Selection'[Metric]) = "Top", [Top Sales by Rank],
	VALUES( Selection[Metric]) = "Bottom", [Bottom Sales by Rank],
	0)

Here i am applying ranking logic applied only on Customer Name.But if i click on region also results should effect.
Thanks

1 Like
6

To me it should actually work as you describe in the forum post.

When you go to select the Region, that additional context should be applied to the calculation and then the RANKX will only perform the ranking for customers in that region.

Are you saying you DON’T want the region filter to impact the calculation at all?

If so you would want to wrap the entire formula in CALCULATE and then use ALL( RegionsColumn)

The formula can also be simplified somewhat by using the ones in this video vs the ones you’ve used from another.

Have a look at this one below and also the resources that you can download