Forum Discussion

Rob1992's avatar
Rob1992
New Member
6 years ago
Solved

Multiple top N filters

Hi,   I am new to Power BI, but love it already! Amazing tool. Unfortunately I am facing an issue where I am unable to add multiple top N filters to a bar chart.   What I am trying to visualize ...
Need Help
  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    6 years ago

    Hi Rob1992 ,

     

    We can create a calculate column and use filter on this visual to meet your requirement.

     

    1. We need to create a column to calculate the total of each category.

     

    Sum = 
CALCULATE(SUM('Table'[Spend]),FILTER(ALLSELECTED('Table'),EARLIER('Table'[Category])='Table'[Category]))

     

     

    2. Then we can create a rank column based on sum column.

     

    Rank = RANKX('Table','Table'[Sum],,DESC,Dense)

     

     

    3. At last we can put the rank column in filter on this visual and configure the top five of supplier name.

     

     

     

    If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

    BTW, pbix as attached.

     

    Best regards,

     

    Community Support Team _ zhenbw

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

kentyler's avatar
kentyler
Icon for Solution Sage rankSolution Sage
6 years ago

It sounds like you're saying.... if i find the top 10 categories (based on spend) then i would like to show the top 5 suppliers...in those categories

You should be able to write a measure that can be applied to the supplier table that looks up the top ten categories, and then checks to see if the current supplier is in the top 5 spenders for those categories.... which returns a flag value, true/false that tells whether the supplier should be included in the report.

If you'd like help with the dax please post a sample table (from excel) of suppliers, and of spending and I'll help you with the measure.

Rob1992's avatar
Rob1992
New Member
6 years ago

Hi, thanks for the quick response. You are correct, that is what I am trying to do.

 

Again, I am completely new to PBI so writing measures is new too.

 

I have anonymized the supplier names and categories in the sample below.

 

Supplier - NameCategorySpend
ChrisA2,28
MarkB8,43
ChrisB13,58
ChrisC2,61
ChrisC2,81
ChrisB14,38
ChrisD94,98
ChrisE9,77
ChrisF4,63
ChrisE12,9
ChrisD67,42
ChrisF9,03
ChrisE19,87
ChrisF15,91
ChrisC24,86
ChrisB4,78
ChrisF21,51
ChrisE12,86
ChrisE28,8
ChrisB14,82
ChrisC2,06
PeterG10,64
TedG7,8
TedH2,02
TedG10,55
TedH1,96
TedH0,96
MarkH27,6
MarkI53,7
MarkH20,28
MarkI9,5
MarkI5,46
MarkB16,86
MarkE9,39
MarkE11,61
MarkE9,24
MarkB16,5
MarkB6,84
MarkH2,94
MarkB10,52
MarkB56,25
MarkI10,32
MarkH27,72
MarkI20,79
MarkI17,04
MarkB4,23
MarkB11,28
MarkB11,28
MarkB8,51
MarkH4,14
MarkB9,24
MarkB16,06
MarkI11,91
MarkG12,03
MarkB7,6
MarkI20,09
MarkI20,84
MarkG22,52
MarkG25,96
MarkG22,76
MarkH4,14
MarkB7,52
MarkJ16,98
MarkH16,49
MarkB6,47
MarkG12,51
MarkB11,06
MarkI5,58
MarkI39,29
MarkG16,92
MarkG21,48
MarkH4,86
MarkC8,24
MarkG10,2
MarkG31,33
MarkH17,82
MarkH2,98
MarkH7,86
MarkI19,05
MarkI18,55
MarkB27,62
MarkB21,07
MarkB22,12
MarkA37,36
MarkC9,76
MarkE14,4
MarkC12,66
MarkC23,15
MarkD17,7
MarkC14,71
MarkK26,72
MarkK75,32
MarkC6,39
MarkC12,54
MarkC9,81
MarkL29,4
MarkC14,37
MarkD14,72
MarkD14,72
  • kentyler's avatar
    kentyler
    Icon for Solution Sage rankSolution Sage
    6 years ago

    Working out a solution to this involved multiple, interlocked steps, so a made a little video to explain them. https://youtu.be/nJrbc6P209o  Let me know if you have any further questions.