Forum Discussion

Frequent Visitor

2 years ago

Solved

Create a dynamic table from a slicer

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,...

Need Help

SENQLDHLTH
2 years ago
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:
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"

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

SENQLDHLTH

Frequent Visitor

2 years ago

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:

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"
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"
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

Forum Discussion

Create a dynamic table from a slicer

Related content

Creating a dynamic slicer

Dynamic Subtitle with Hierarchy Slicers

Power BI Dynamic Filter/Slicer

Show slicers dynamically

Dynamic Complex Filtering with slicers