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

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

     

     

     

     

    • v-haibl-msft's avatar
      v-haibl-msft
      Microsoft Employee
      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