Grouping categories in single column for slicer

[Anonymous]

Hello,

I have a list of accounts which have been categorised in a single column to indicate whether they are in the 'Top 10', 'Top 50', 'Top 100' firms etc.

At the moment, when I use a slicer and click 'T10' it will show the top 10 accounts, however, when I select 'T50' it will only show those categorised as 'T50' and not the 'T10' accounts as well.

How could I transform the data so that:

- When click 'T50' on slicer it would show: account categorised as T50 and T10

- When click 'T100' on slicer it would show: accounts categorised as T100, T50 and T10 etc.

Hope this makes sense. Would appreciate any guidance.

Many thanks.

[nandukrishnavs]

@Anonymous 

Rank Category

Top 10

Top 20

Top 50

Company	Sales
Company1	100
Company2	120
Company3	140
Company4	160
Company5	180
Company6	200
Company7	220
Company8	240
Company9	260
Company10	280
Company11	300
Company12	320
Company13	340
Company14	360
Company15	380
Company16	400
Company17	420
Company18	440
Company19	460
Company20	480
Company21	500
Company22	520
Company23	540
Company24	560
Company25	580
Company26	600
Company27	620
Company28	640
Company29	660
Company30	680
Company31	700
Company32	720
Company33	740
Company34	760
Company35	780
Company36	800
Company37	820
Company38	840
Company39	860
Company40	880
Company41	900
Company42	920
Company43	940
Company44	960
Company45	980
Company46	1000
Company47	1020
Company48	1040
Company49	1060
Company50	1080
Company51	1100
Company52	1120
Company53	1140
Company54	1160
Company55	1180
Company56	1200
Company57	1220
Company58	1240
Company59	1260
Company60	1280
Company61	1300
Company62	1320
Company63	1340
Company64	1360
Company65	1380
Company66	1400
Company67	1420
Company68	1440
Company69	1460
Company70	1480
Company71	1500
Company72	1520
Company73	1540
Company74	1560
Company75	1580
Company76	1600
Company77	1620
Company78	1640
Company79	1660
Company80	1680
Company81	1700
Company82	1720
Company83	1740
Company84	1760
Company85	1780
Company86	1800
Company87	1820
Company88	1840

Calculated Column 

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

Measure

FilterMeasure = 
var _slectedCategory= CALCULATE(SELECTEDVALUE(RankSlicer[Rank Category]))
var _selectedRank= CALCULATE(SELECTEDVALUE('Table'[Rank]))
var _result= SWITCH(_slectedCategory,"Top 10",IF(_selectedRank<=10,"Show"),"Top 20",IF(_selectedRank<=20,"Show"),"Top 50",IF(_selectedRank<=50,"Show"))
return _result

Now you can apply a visual level filter to show only required values.

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

[Anonymous]

Thanks very much for your response.

I'm quite new to PBI so apologies for my basic question but if I already have a column in the data which ranks accounts from 1-100 I wouldn't need to do the first step, right? If the name of this column is 'SalesRank', where in the formula would I refer to this column?

Thanks again.

[nandukrishnavs]

@Anonymous  If you already have the Rank column, you don't need to calculate it again. 

I would recommend you to implement the above solution, so you will understand the steps. Then you can tweak the logic based on your requirement.

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

[AllisonKennedy]

In that case, try this: 

First create a new TABLE to get:

Category	Rank
T10	10
T20	20
T50	50

If your Category just has T before the number, (ie 1 character), then you can use this DAX to create the table (though it is preferred to create it in Query Editor):

CatInd = ADDCOLUMNS(VALUES(Accounts[Category]),"Rank", RIGHT(Accounts[Category],LEN(Accounts[Category])-1))

Then your MEASURE formula will be: 

Show = IF(SELECTEDVALUE(CatIndex[Rank], 1000)>=SELECTEDVALUE(Accounts[SalesRank],0),"Yes","No")

The 1000 number ensures that if nothing is selected in the slicer, all accounts will display, so make that number higher if needed. 

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

[Anonymous]

Apologies again but I have created measure as instructed but not sure how to apply this measure to the slicer / visual. Can you please explain? 

Thanks again

[nandukrishnavs]

@Anonymous 

Click slicer visul and drag Rank Category column to its value field. Click table visul and add required columns to its value field. Now select newly created table visual and goto its filter section ( right side ). Drag "FilterMeasure" into visual level filter and set show value is show.

https://www.sqlbi.com/articles/applying-a-measure-filter-in-power-bi/ 

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

[AllisonKennedy]

You will need to have the categories indexed in a separate table (not with the list of accounts table) and not related to the accounts table:

CatIndex = 

Category	Index
Top 10	1
Top 50	2
Top 100	3

Use that CatIndex[Category] for your slicer.

Ideally you will also have the CatIndex as a COLUMN in the accounts table: 

CatIndex=SWITCH(Accounts[Category], "Top 10", 1, "Top 50", 2, "Top 100", 3)

Then create a new MEASURE to decide whether to Display the row or not: 

Display = IF(SELECTEDVALUE(CatIndex[Index],100)>=SELECTEDVALUE(Accounts[CatIndex], 0),"Yes", "No")

Finally, use the new measure as a Visual Level filter on the matrix and set filter to Yes only.

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

[nandukrishnavs]

@Anonymous 

Refer to this example https://community.powerbi.com/t5/Desktop/Dynamic-TOP-N-list/m-p/1091863/highlight/true#M505718

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

[nandukrishnavs]

@Anonymous 

First, you have to create a Rank column. https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns

Then create another table with Rank Group category. Now you can use that Rank category in a slicer. And create a Measure to filter the Table based on selected Rank. You can use SELECTVALUE() for getting the selected category and check if the rank is less than or equal to rank value. You can use this measure as a visual level filter.

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂