Populating Top N on a Consistent Level Regardless of Slicer

[AW11]

Hello,

I am trying to show the Top 5 Level 7 accounts by sales (which is a measure). However, you can slice the page on Level 5, 6 or 7. No matter what the slicer is, I want to show the Top 5 Level 7 accounts of that Level 5, Level 6 or all for that matter. I am running into an issue where more than 5 are showing up. The Account hierarchy and the sales data are in separate tables, connected by a unique Account ID of the lowest level (lower than level 7).

Any help would be much appreciated.

Best, 

[AW11]

I was able to show top 5 Level 7 no matter the slicer using this DAX but now need to show bottom 5 as well and simply updating to ASC order is not working.

Rank Level 7 DESC =

VAR _test = NOT(ISBLANK([sales]))

VAR _result = RANKX(

    ALLSELECTED('_Account Dimension'[Level 7 Desc]),[sales])

RETURN

IF(_test,_result)

[AW11 · Accepted solution]

I needed to add the following filter to not rank blanks or deminimis values.

Rank Level 7 ASC =

VAR _test = AND(NOT(ISBLANK([sales])),[sales]>0)

VAR _result =

IF(

    [sales]=0,BLANK(),

RANKX(

    FILTER(

    ALLSELECTED('_Account Dimension'[Level 7 Desc]),[sales]>0),[sales],,ASC,Dense))

RETURN

IF(_test,_result)

[AW11]

I get to here and then if I try to filter on rank for anything less than 6 the table goes blank 

[v-rzhou-msft]

Hi @AW11 ,

On myside, I couldn't reproduce your issue. It works well.

Please share a sample file with us and show a screenshot to us with the result you want.

You can download the attatchments to compare the data model with yours.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AW11]

I get to here and then if I try to filter on rank for anything less than 6 the table goes blank 

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result.

[AW11]

Dept ID	Level 1	Level 2	Level 3	Level 4	Level 5	Level 6	Level 7	Level 8
1	ABC Company	West	TOP	XXX	Tundra	Xbox	Red	Park
2	ABC Company	West	TOP	XXX	Tundra	Xbox	Green	Grass
3	ABC Company	West	TOP	XXX	Charlie	Switch	Blue	Tree
4	ABC Company	East	LARGE	YYY	Mike	PSP	Yellow	Bush
5	ABC Company	East	LARGE	YYY	Mike	PSP	Yellow	Sky
6	ABC Company	East	LARGE	YYY	Mike	PSP	Yellow	Water
7	ABC Company	East	LARGE	YYY	Mike	PSP	Purple	Ocean
8	ABC Company	East	LARGE	YYY	Mike	PSP	Teal	Dirt
9	ABC Company	East	LARGE	YYY	Mike	Android	Brown	Road
10	ABC Company	East	LARGE	YYY	Mike	Android	Lilac	Gas
11	ABC Company	East	LARGE	YYY	Mike	Android	Lincoln	Protein
12	ABC Company	East	LARGE	YYY	Durango	Android	Bush	Lipid

Sales Data

Dept ID	Products Sold	Price	Sales Measure
1	44	2	6
2	4	2	8
3	5	3	15
4	6	4	24
5	7	5	35
6	8	3	24
7	9	4	36
8	10	5	50
9	11	3	33
10	15	2	30
11	13	1	13
12	14	1	14

If data is sliced on Level 5 "Mike", I would expect the following level 7s to show:

Yellow, Purple, Teal, Brown, and Lilac

If the data is not slice on Account at all, I would expect the following level 7s to show:

Red, Yellow, Teal, Purple, and Brown

If the data is slided on Level 6 "Xbox", I would expect the following level 7s to show:

Red and Green.

[Ashish_Mathur]

I cannot understand your question.  Someone who does will help.

[AW11]

replying for exposure. Why is there so much spam in this forum?