Dynamic Top N with hirarchy in matrix Visual

[sarjensystems1]

Hii

i have a requirement to display dynamic Top N category based on country and i am using Matrix visual

i am using this DAX function

TopN Sales=

VAR SelectedTop = SELECTEDVALUE('TopN'[TopN])

RETURN

SWITCH(TRUE(),

SelectedTop = 0, [sales],

RANKX (

ALL( Query1[Category]),

[sales]

)

<= SelectedTop,

[sales]

)

this is working fine...but when i drag subcategory to the matrix it is displaying wrong values...(filtering both category and subcategory)

my requirement is to display top N Category Values with out filtering the subcategory...

How to do this???

Thanks....

[v-kelly-msft · Accepted solution]

Hi @sarjensystems1 ,

I have changed your measure and I guess this time it will satisfy your need:

TopN Sales = 
VAR SelectedTop = SELECTEDVALUE('TopN'[TopN])
RETURN
SWITCH(TRUE(),
SelectedTop = 0, [sales],
RANKX ( 
ALL('Table'),
[sales]
)
<= SelectedTop,
[sales]
)

And you will see:

For the related .pbix file,pls click here.

 
Best Regards,
Kelly

[v-kelly-msft]

Hi @sarjensystems1 ，

I'm not sure whether [sales] is a column or measure,but I tried to use your sample data and create a measure names [sales],when I select subcategory,it appears right, as you can see below:

For the related .pbix file,pls click here.

Best Regards,

Kelly

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[amitchandak]

I did not test it but

You might have to try ALLEXCEPT( Query1[Category]) in place of ALL( Query1[Category]).

Refer

https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures