Forum Discussion

JosephL's avatar
JosephL
Regular Visitor
8 years ago
Solved

Combining Top N filters

Dear All,

I am new to Power BI Desktop and would like some help here.

 

I am trying to find a method to compare the Top N filtered companies by the profit of two separate periods so that the Top N companies of each period both show up together, then the change of profit of those companies of the two periods could be compared.

 

Thanks in advance.

Joseph

 

I wasn't able to find my message after I posted it so I reposted it again, sorry if I made duplicate posts.

  • JosephL's avatar
    JosephL
    8 years ago

    Nevermind, I have found the way to solve the problem myself, still thanks v-yuta-msft for attempting to solve my problem.

4 Replies

  • JosephL's avatar
    JosephL
    Regular Visitor
    8 years ago

    Dear All,

    I am quite new to Power BI Desktop and I am still wrapping my head around the functions of this software.

     

    I am currently trying to compare the sales of various companies and I would like to display only the Top N companies, e.g. 5, based on the sales of either the specified period in a year (monthly or weekly based on a date table of mine) and the period of the year before, which results in at least 5 companies and a maximum of 10 shown, if the sales vary significantly between years.

     

    I would like some guidance on this matter and thanks in advance.

  • v-yuta-msft's avatar
    v-yuta-msft
    Icon for Community Support rankCommunity Support
    8 years ago

    Hi JosephL,

     

    Your requirement seems like to rank profits of company by period and achieve top N profits, right? If my understanding is right, you can create a calculate column using DAX formula like pattern below and check if it works well in your senario:

    Rank by Period =
RANKX (
    FILTER ( Table, Table[Period] = EARLIER ( Table[Period] ) ),
    RANKX ( ALL ( Table ), Table[Profit] ),
    ,
    ASC,
    DENSE
)

    Then you can use visual level filter to achieve the top N profit based on the rank column in each period.

     

    If my understanding is not so correct, could you please show some tables or DAX formula for further research?

     

    Regards,

    Jimmy Tao

    • JosephL's avatar
      JosephL
      Regular Visitor
      8 years ago

      I am trying to do a comparison between this years profit and previous years profit through the slicers i added above, with year showing the current year and the previous year gp is calculated. The month slicer and week number slicer are further used to select a period of the years.

       

      What I am trying to do is filter the companies by either being the top n of the current year or being the top n in the previous year.

       

      On the left are the company names.

       

      Hope that my clarification is clear.

      • JosephL's avatar
        JosephL
        Regular Visitor
        8 years ago

        Nevermind, I have found the way to solve the problem myself, still thanks v-yuta-msft for attempting to solve my problem.