cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BogdanP Frequent Visitor
Frequent Visitor

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:

Graph sample.PNG

 

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:

Graph2.PNG

 

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
v-haibl-msft Super Contributor
Super Contributor

Re: Setting up static TopN filter

@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 5
v-haibl-msft Super Contributor
Super Contributor

Re: Setting up static TopN filter

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

Setting up static TopN filter_1.jpg

 

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

Setting up static TopN filter_2.jpg

 

Best Regards,

Herbert

BogdanP Frequent Visitor
Frequent Visitor

Re: Setting up static TopN filter

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

вырезка.PNG

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 Super Contributor
Super Contributor

Re: Setting up static TopN filter

@BogdanP

 

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

 

Best Regards,

Herbert

BogdanP Frequent Visitor
Frequent Visitor

Re: Setting up static TopN filter

@v-haibl-msft

 

Sure. Here are the relationships:

Data_Scheme.PNG

 

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

структура.PNG

 

 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

 

 

 

 

Highlighted
v-haibl-msft Super Contributor
Super Contributor

Re: Setting up static TopN filter

@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

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 6 members 2,941 guests
Please welcome our newest community members: