Create a dynamic table from a slicer

Question

HiI have been trying for several days, unsuccesful I may say, to create a visualisation which shows the top N items (by number of items) and anything remaining to be shown as "Other"For instance, in the database that I am about to share, overall "Item 5" have the most amount followed "Item 3". Now, if I want a graph with top 20% of Items, the graph should show, as detailed, Items 5 and 3 and all other items (1, 2, 4 - 10) as "Other". Now if I chose the top 50% of the items the graph should show, as detailed, Items 5; 3; 1; 10; and 7 and all other items (2, 4, 6, 8 and 9) as "Other". Now, if I filter by "Location D" and "Description A" and want to see only 30% of top Item, the graph should show, as detailed, Item 9 and all other items (6,10 and 7 - no amount with these filters for items 1, 2, 3, 4, 5 and 😎 as "Other". I have tried to use this approach (https://community.powerbi.com/t5/Desktop/Create-quot-Other-quot-Category-in-Pareto-Chart/td-p/2139483), but it is quite limited in the number of filters I can use.At the end, I just want at ble which I can use for visualisation purposes and can be filtered by the fields of the table (In this case, Location, Description, Top N items and Date)I have been able to create a dynamic ranking (measure) based on the amount and the % top item.Here the link to the database&nbsp;https://www.dropbox.com/scl/fi/n8ywnlo1j97qdyz3wm385/Sample-data.xlsx?rlkey=gjzpktjf5o4gxo1qpo4u00akf&amp;dl=0&nbsp;I hope you can help meKind Regards,Carlos&nbsp;

senqldhlth · Accepted Answer

Hi Community&nbsp;I was able to solve this&nbsp;Step 1: Create a measure for total amountTotal =SUMX (&nbsp;&nbsp;&nbsp;&nbsp;Table1,&nbsp;&nbsp;&nbsp;&nbsp;Table1[Amount])&nbsp;Step 2: Create a Paremet (From 0 to 1, incresing by 0.1)Resulting in:Percent = GENERATESERIES(0, 1, 0.1)Percent Value = SELECTEDVALUE('Percent'[Percent])&nbsp;&nbsp;Step 3: Create a table to include "Other" itemTop Items =&nbsp; &nbsp; UNION(&nbsp; &nbsp; &nbsp; &nbsp; VALUES(Table1[Item]),&nbsp; &nbsp; &nbsp; &nbsp; ROW("Item","Other"))&nbsp;Step 4: Create a measure to rank items in new table&nbsp;&nbsp;Item Ranking Top =IF (&nbsp;&nbsp;&nbsp;&nbsp;NOT (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ISBLANK ( [Total] )&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;IF (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ISINSCOPE ( 'Top Items'[Item] ),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;RANKX (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FILTER (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ALLSELECTED ( 'Top Items'[Item] ),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;NOT (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ISBLANK ( [Total] )&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[Total]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;))&nbsp;Step 5: Create a measure to obtain the maximum ranking of Step 4Max Ranking Top =MAXX (&nbsp;&nbsp;&nbsp;&nbsp;ALLSELECTED ( 'Top Items'[Item] ),&nbsp;&nbsp;&nbsp;&nbsp;[Item Ranking Top])&nbsp;Step 6: Create a measure to obtain the Top N Spend&nbsp;Top NSpend =VAR TopItems =&nbsp;&nbsp;&nbsp;&nbsp;TOPN (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Percent'[Percent Value] * [Max Ranking Top],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ALLSELECTED ( 'Top Items' ),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[Total]&nbsp;&nbsp;&nbsp;&nbsp;)VAR Allspend =&nbsp;&nbsp;&nbsp;&nbsp;CALCULATE (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[Total],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ALLSELECTED ( 'Top Items' )&nbsp;&nbsp;&nbsp;&nbsp;)VAR Otherspend =&nbsp;&nbsp;&nbsp;&nbsp;Allspend&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;- CALCULATE (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[Total],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;TopItems&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)VAR TopNspend =&nbsp;&nbsp;&nbsp;&nbsp;CALCULATE (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[Total],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;KEEPFILTERS ( TopItems )&nbsp;&nbsp;&nbsp;&nbsp;)VAR currentitems =&nbsp;&nbsp;&nbsp;&nbsp;SELECTEDVALUE ( 'Top Items'[Item] )RETURN&nbsp;&nbsp;&nbsp;&nbsp;IF (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;currentitems = "Other",&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Otherspend,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;TopNspend&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;Step 7: Create a ranking to order the resultsRanking =IF (&nbsp;&nbsp;&nbsp;&nbsp;[Top NSpend] &gt; 0,&nbsp;&nbsp;&nbsp;&nbsp;RANKX (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FILTER (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ALLSELECTED ( 'Top Items'[Item] ),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;NOT (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ISBLANK ( [Total] )&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[Total]&nbsp;&nbsp;&nbsp;&nbsp;))&nbsp;Now, to create the required visualisation, I had:Included a filter for the parameter (single value)Included a filter for year (List)Included a filter for Location (List)Include a filter for Description (List)Included a "Line and clustered column chart"Include field "Item" from table "Top Items" in "X-axis"Include measure "Top NSpend" in "Column y-axis"Include measure "Ranking" in "Line y-axis"Sort axis by "Ranking" and "Sort ascending"&nbsp;Now, below my results replicating excalty what I required in my initial post&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Unfortunatelly, I am not able to upload the PBIX file. Otherwise, happy to share the file&nbsp;Idrissshatila&nbsp;your suggestion/recommendation did not help me at all in getting this solved&nbsp;Regards,&nbsp;Carlos&nbsp;

Forum Discussion

Create a dynamic table from a slicer

2 Replies

Related content

Creating a dynamic slicer

Dynamic Subtitle with Hierarchy Slicers

Power BI Dynamic Filter/Slicer

Show slicers dynamically

Dynamic Complex Filtering with slicers