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 prices which i am intending to visualize using a bar chart:

 

Currently I have a Visual filter which shows only Selected TopN (3,5,7,10) producers in terms of output (Volumes[Volume] column). The problem is that it calculates ranks for TopN producers in each year separately. What I am trying to achieve is showing only this year (2016 atm) TopN. Here is what I have so far:

  1.  Ranking measure:      RanksForAvg = RANKX(ALL(Volumes[Producer Eng]); CALCULATE(SUM(Volumes[Volume]); FILTER(ALL(Dates[Year]); Dates[Year]=YEAR(NOW()))); ;DESC;Dense)

where Volumes[Producer Eng] is just Producer's name

     2. Visual filter measure: FiltForAvg = IF([RanksForAvg]<=[Selected TopN];1;0)

 

Both work just fine as standalone visualizations, but when I try to add visual filter it behaves really odd, filtering nothing:

 

 

I hope PowerBI community could help me with this one. Thank you all in advance.

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

5 Replies

  • 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