Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Dynamic TopN with RankX

Hello everyone,   I have a base with a lot of products, and i want to show the sales of each product along the time in a line chart.  To make the chart more clean, i want to make a dynamic top filt...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    It is unclear on what you mean with "make it work different".

     

    Are you trying to show the 4 items with the highest total sales over a year? or just during the month?

     

    EDIT: 

    If took your example file and created the following measure:

    SubTotal = CALCULATE(SUM(Base[Valor Total]);ALLEXCEPT(Base;Base[marca]))

    I then changed your TopN Measure to:

    TopN_total_sales = 
VAR SelectedTop = SELECTEDVALUE('TopN'[TopN])
var RankMarca = RANKX(ALL(Base[marca]);[SubTotal])

RETURN
IF(RANKX(ALL(Base[marca]);[SubTotal])<=SelectedTop;[total_sales];BLANK())

     

    I'll get this as a result:

    Result

    I hope that this solves your problem!

     

    Kind Regards.

     

    PS: If it does solve your problem please mark this as answer.

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi Anonymous,

 

The Visual you've created does exactly what it needs to be done.

It shows the top from that month that's why you'll get blank rows in between because Blue is ranked 4th on may june july and august and orange is ranked 4th on the other months.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Yes.. I know how it is working, but the question is how to make it work different.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      It is unclear on what you mean with "make it work different".

       

      Are you trying to show the 4 items with the highest total sales over a year? or just during the month?

       

      EDIT: 

      If took your example file and created the following measure:

      SubTotal = CALCULATE(SUM(Base[Valor Total]);ALLEXCEPT(Base;Base[marca]))

      I then changed your TopN Measure to:

      TopN_total_sales = 
VAR SelectedTop = SELECTEDVALUE('TopN'[TopN])
var RankMarca = RANKX(ALL(Base[marca]);[SubTotal])

RETURN
IF(RANKX(ALL(Base[marca]);[SubTotal])<=SelectedTop;[total_sales];BLANK())

       

      I'll get this as a result:

      Result

      I hope that this solves your problem!

       

      Kind Regards.

       

      PS: If it does solve your problem please mark this as answer.

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Thank you , it worked perfectly!

         

        In the TopN Measure, the var RankMarca is not used, so i deleted it. And [GrandTotal] should be [SubTotal], right?

         

        Thanks