1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

how to use PowerBi filter top 10 negative number

Discussion in 'Power Pivot, Power Map etc' started by Oscarr, Mar 13, 2018.

  1. Oscarr

    Oscarr Member

    Messages:
    79
    Hi all

    anyone know how to use PowerBi to filter Top 10 negative number?

    example:
    kent -2000
    jack -3000
    kiki 2000
    yuki -1000
    yoyo 3000

    i want result:
    Jack -3000
    kent -2000
    yuki -1000

    Thanks

    Attached Files:

  2. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    856
    Hi Oscarr, you show a power query. Why not filter the table?
    Filter negative numbers, sort ascending, add an index, filter on index <10 (if index starts from 0).
    But remember this is an excel forum, not a powerBi forum.
  3. Oscarr

    Oscarr Member

    Messages:
    79
    hi @GraH - Guido

    i want filter at outside, like have got button top 3 top 5 top 10..

    i thought here can post powerbi, may i know where can post powerbi?

    Thanks
  4. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    856
    There is a technique "disconnected table", I think you can adopt.
    I did not suggest you cannot post anything about PowerBI. I just wanted to point out, that it is perhaps not the best place. Alternative is the offical PowerBI forum from MS. If you consider cross posting, please let us know.
  5. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    856
    Would it be like this, what you are after (did it in Excel PowerPivot)?

    Attached Files:

  6. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,115
    To set to Top/Bottom N filter via Slicer in PowerBI, there are several ways.

    Here's one method.

    1. Create new table with following formula. Create relationship to original table based on Name column.
    Code (vb):
    RefTable = GROUPBY(Table1,Table1[Name],"Total_Profit",SUMX(CURRENTGROUP(),Table1[Profit]))
    2. Create following measures in the original table.
    Code (vb):
    Sum_Profit = SUM(Table1[Profit])
    Code (vb):
    RankAsc = RANKX(ALL('RefTable'),[Sum_Profit],,ASC)
    3. Create another table like below.
    Code (vb):
    TopN_Asc = DATATABLE("TopN",INTEGER,{{3},{5},{10}})
    4. Add measure to TopN_Asc table created.
    Code (vb):
    MinTopN = MIN(TopN_Asc[TopN])
    5. Finally add measure to original table.
    Code (vb):
    Flag = IF([RankAsc]<=[MinTopN],1,0)
    Now to set up visual.
    1. Add slicer and add TopN column from TopN_Asc.
    2. Add Matrix and set up as below.

    upload_2018-3-13_12-2-5.png
    Oscarr and GraH - Guido like this.
  7. r2c2

    r2c2 Active Member

    Messages:
    154
    @Oscarr I think its time we added sub-forums on Power BI, Power Pivot (and rename this one to Power Query).

    You can use the approach @Chihiro suggested. Alternatively, you can use the topn filtering natively available in PBI. Just create a measure on which you want to Top N. Put it in the table (or a chart).
    Then while keeping the visual selected, click on Filters and choose "Top N" filtering. Use the measure to filter values. Enter N value as you want.
    GraH - Guido likes this.
  8. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,115
    @Oscarr
    Only time I'd use slicer selection to filter for TopN is when I want to publish to Report/Dashboard/App and when user requests ability to flip between different filter options. Since their access to filter pane is restricted in most cases (1 or 2 admin only).

    What @r2c2 wrote is by far most stable method and has better performance, if you are the only user, everyone has filter pane access, or it's being done on PowerBI Desktop.
  9. Oscarr

    Oscarr Member

    Messages:
    79

    hi @Chihiro

    that first one create from where? powerbi? can show me pic where to make all that?

    i want that results is separate top 3 negative value client and top 3 positive value client...can make that negative and positive button to choose ?

    Thanks

    Attached Files:

  10. Oscarr

    Oscarr Member

    Messages:
    79
    Hi @Chihiro

    So how about this? can you help and make it?
  11. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,115
    To add table just go to Modeling tab and click on New Table and enter the formula.

    As for another measure using TopN in descending order. Add measure with following. And follow steps explained in my original post.
    Code (vb):
    RankDesc = RANKX(ALL('RefTable'),[Sum_Profit],,DESC)
    Edit: I don't have time to test it now, but I'd imagine it would conflict with the original filter. So you'd need additional logic to turn off the first filter when 2nd slicer is selected and vice versa.

    Basically additional IF nested in original flag check.

    Or simply do it in separate page.

Share This Page