Forum Discussion

sarjensystems1's avatar
sarjensystems1
Icon for Helper III rankHelper III
6 years ago
Solved

Dynamic Top N with hirarchy in matrix Visual

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....
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago

    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

4 Replies

  • v-kelly-msft's avatar
    v-kelly-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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

  • v-kelly-msft's avatar
    v-kelly-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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