Forum Discussion

BogdanP's avatar
BogdanP
Frequent Visitor
10 years ago
Solved

Setting up static TopN filter

Hello everyone!   I have standard transactions table and a date table. Transactions table contains info on various producers and their output. I created a simple measure to calculate their average ...
Need Help
  • v-haibl-msft's avatar
    v-haibl-msft
    10 years ago

    BogdanP

     

    In this scenario, I think we can first create a new TOPN table for 2016. 2016 Topn Producer Eng will be in this table. Then create a measure to calculate the volumes for each Producer Eng in a specific year (which will be on the X-Axis). At last, drag this volumes measure into Value field, the column chart will always show the volumes of 2016 topn producer eng for each year.

     

    Best Regards,

    Herbert

v-haibl-msft's avatar
v-haibl-msft
Microsoft Employee
10 years ago

BogdanP

 

In this scenario, you can first create a year column and then create a rank column (only rank volume for 2016 and return 1 for other years) in Volumes table with following formula.

Year = 
YEAR ( Volumes[Date] )
Rank = 
IF (
    Volumes[Year] = YEAR ( TODAY () ),
    RANKX (
        FILTER ( Volumes, Volumes[Year] = YEAR ( TODAY () ) ),
        Volumes[Volume],
        ,
        DESC,
        DENSE
    ),
    1
)

 

Drag Rank column into Visual level filters and select TopN as you wanted.

 

Best Regards,

Herbert

  • BogdanP's avatar
    BogdanP
    Frequent Visitor
    10 years ago

    v-haibl-msft

     

    Thank you very much for your response! It was very helpful - now I understand how RANKX and FILTER work way better.

     

    I apologise as I should've been clearer at describing my data and what am I trying to achieve here.

    Let's say we have top5 in 2016: A B D C E

    top5 in 2015: A C B F D

    What i want is seeing top5 2016 development through the years, so in 2015 I also want to see A B D C E producers, regardless of what top5 in 2015 was.

     

    And one more thing worth mentioning - Producer Eng isn't unique, it's inherited from Producers table, so if we are ranking entire volumes table as you proposed we get something looking like this:

    which is wrong because i want to rank Producer Eng, not Producer.

     

    I am not asking you for a solution, just for a hint, because i realise that it can be really time-consuming for you and that i should've describe my problem better.

     

    Thank you in advance,

    Bogdan

    • v-haibl-msft's avatar
      v-haibl-msft
      Microsoft Employee
      10 years ago

      BogdanP

       

      Could you please provide some more information like columns in your each table and relationship between tables?

       

      Best Regards,

      Herbert

      • BogdanP's avatar
        BogdanP
        Frequent Visitor
        10 years ago

        v-haibl-msft

         

        Sure. Here are the relationships:

         

         And there is a full list of "Volumes" table fields:

         

         In this analysis I am also considering only the values where dimension = "mln l". Table GroupsVolume do not take any part in currently discussed visualizations.

         

        Thank you in advance.

         

        Best Regards, 

        Bogdan