Create a dynamic table from a slicer

[SENQLDHLTH]

Hi

I 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/213948...), 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 https://www.dropbox.com/scl/fi/n8ywnlo1j97qdyz3wm385/Sample-data.xlsx?rlkey=gjzpktjf5o4gxo1qpo4u00ak... 

I hope you can help me

Kind Regards,

Carlos 

[SENQLDHLTH · Accepted solution]

Hi Community

I was able to solve this

Step 1: Create a measure for total amount

Total =
SUMX (
    Table1,
    Table1[Amount]
)

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])

Step 3: Create a table to include "Other" item

Top Items =

    UNION(

        VALUES(Table1[Item]),

        ROW("Item","Other"))

Step 4: Create a measure to rank items in new table

Item Ranking Top =
IF (
    NOT (
        ISBLANK ( [Total] )
    ),
    IF (
        ISINSCOPE ( 'Top Items'[Item] ),
        RANKX (
            FILTER (
                ALLSELECTED ( 'Top Items'[Item] ),
                NOT (
                    ISBLANK ( [Total] )
                )
            ),
            [Total]
        )
    )
)

Step 5: Create a measure to obtain the maximum ranking of Step 4

Max Ranking Top =
MAXX (
    ALLSELECTED ( 'Top Items'[Item] ),
    [Item Ranking Top]
)

Step 6: Create a measure to obtain the Top N Spend

 Top NSpend =
VAR TopItems =
    TOPN (
        'Percent'[Percent Value] * [Max Ranking Top],
        ALLSELECTED ( 'Top Items' ),
        [Total]
    )
VAR Allspend =
    CALCULATE (
        [Total],
        ALLSELECTED ( 'Top Items' )
    )
VAR Otherspend =
    Allspend
        - CALCULATE (
            [Total],
            TopItems
        )
VAR TopNspend =
    CALCULATE (
        [Total],
        KEEPFILTERS ( TopItems )
    )
VAR currentitems =
    SELECTEDVALUE ( 'Top Items'[Item] )
RETURN
    IF (
        currentitems = "Other",
        Otherspend,
        TopNspend
    )

Step 7: Create a ranking to order the results

Ranking =
IF (
    [Top NSpend] > 0,
    RANKX (
        FILTER (
            ALLSELECTED ( 'Top Items'[Item] ),
            NOT (
                ISBLANK ( [Total] )
            )
        ),
        [Total]
    )
)

Now, to create the required visualisation, I had:

1. Included a filter for the parameter (single value)
2. Included a filter for year (List)
3. Included a filter for Location (List)
4. Include a filter for Description (List)
5. Included a "Line and clustered column chart"
   1. Include field "Item" from table "Top Items" in "X-axis"
   2. Include measure "Top NSpend" in "Column y-axis"
   3. Include measure "Ranking" in "Line y-axis"
6. Sort axis by "Ranking" and "Sort ascending"

Now, below my results replicating excalty what I required in my initial post

 Unfortunatelly, I am not able to upload the PBIX file. Otherwise, happy to share the file

@Idrissshatila your suggestion/recommendation did not help me at all in getting this solved

Regards,

Carlos

[Idrissshatila]

Hello @SENQLDHLTH ,

check the concept of field Parameters.

https://learn.microsoft.com/en-us/power-bi/create-reports/power-bi-field-parameters