Setting up static TopN filter

[BogdanP]

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.

[v-haibl-msft]

@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]

@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]

@BogdanP

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

Best Regards,

Herbert

[BogdanP]

@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 · Accepted solution]

@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