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.
RefTable = GROUPBY(Table1,Table1[Name],"Total_Profit",SUMX(CURRENTGROUP(),Table1[Profit]))
Sum_Profit = SUM(Table1[Profit])
RankAsc = RANKX(ALL('RefTable'),[Sum_Profit],,ASC)
TopN_Asc = DATATABLE("TopN",INTEGER,{{3},{5},{10}})
MinTopN = MIN(TopN_Asc[TopN])
Flag = IF([RankAsc]<=[MinTopN],1,0)
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:RefTable = GROUPBY(Table1,Table1[Name],"Total_Profit",SUMX(CURRENTGROUP(),Table1[Profit]))
2. Create following measures in the original table.
Code:Sum_Profit = SUM(Table1[Profit])
Code:RankAsc = RANKX(ALL('RefTable'),[Sum_Profit],,ASC)
3. Create another table like below.
Code:TopN_Asc = DATATABLE("TopN",INTEGER,{{3},{5},{10}})
4. Add measure to TopN_Asc table created.
Code:MinTopN = MIN(TopN_Asc[TopN])
5. Finally add measure to original table.
Code: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.
View attachment 50674
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
RankDesc = RANKX(ALL('RefTable'),[Sum_Profit],,DESC)