Forum Discussion

AmarB7's avatar
AmarB7
Regular Visitor
3 years ago
Solved

Help for Power BI slicers

Hi dear community,


I have a question about slicers in POWER BI desktop.

I would like to select a value in a slicer to show it in a visual,

but i want others values (that i didnt select) to appear in the visual as "Others".
Like there is "a", "b" and "c", i want to select "a" for the visual and i want "b" and "c" to appear as "others".

Can someone help me with it ?

Thank you guys a lot ! ❤️

  • Check out this article by SQL BI. Although you don't need the TOPN functionality I think that their method for generating a row for Others will work in your situation too.

  • Here is one way.

    Create a Dimension table for the slicer values (in my example 'Category Table'), and then another disconnected table to use in the visual following the code along the lines of:

    Table Cat with Other =
    VAR _Other = { ( "Other", 10000000000 ) }
    VAR _Cats =
        SUMMARIZE (
            FTable,
            FTable[Category],
            "Order", RANKX ( ALL ( FTable[Category] ), FTable[Category],, ASC )
        )
    RETURN
        UNION ( _Cats, _Other )
    

     

    The measure you need for the visual:

    Sum Value =
    VAR _Sales =
        CALCULATE (
            SUM ( FTable[Value ] ),
            TREATAS ( VALUES ( 'Table Cat with Other'[Category] ), FTable[Category] )
        )
    VAR _ALL =
        CALCULATE ( SUM ( FTable[Value ] ), ALL ( 'Category Table'[Category] ) )
    VAR _OtherValue =
        _ALL
            - CALCULATE ( SUM ( FTable[Value ] ), ALLSELECTED ( 'Category Table'[Category] ) )
    VAR _IfFilt =
        IF (
            ISINSCOPE ( 'Table Cat with Other'[Category] ),
            IF ( MAX ( 'Table Cat with Other'[Category] ) = "Other", _OtherValue, _Sales ),
            _ALL
        )
    VAR _NotFilt =
        IF (
            AND (
                ISINSCOPE ( 'Table Cat with Other'[Category] ),
                MAX ( 'Table Cat with Other'[Category] ) = "Other"
            ),
            BLANK (),
            _Sales
        )
    RETURN
        IF ( ISFILTERED ( 'Category Table'[Category] ), _IfFilt, _NotFilt )
    

    Next set up the page with the slicer from the Category Dimension Table, and the visual with the category field from the disconnected table and you will get:

    Sample PBIX attached

3 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion

    Here is one way.

    Create a Dimension table for the slicer values (in my example 'Category Table'), and then another disconnected table to use in the visual following the code along the lines of:

    Table Cat with Other =
    VAR _Other = { ( "Other", 10000000000 ) }
    VAR _Cats =
        SUMMARIZE (
            FTable,
            FTable[Category],
            "Order", RANKX ( ALL ( FTable[Category] ), FTable[Category],, ASC )
        )
    RETURN
        UNION ( _Cats, _Other )
    

     

    The measure you need for the visual:

    Sum Value =
    VAR _Sales =
        CALCULATE (
            SUM ( FTable[Value ] ),
            TREATAS ( VALUES ( 'Table Cat with Other'[Category] ), FTable[Category] )
        )
    VAR _ALL =
        CALCULATE ( SUM ( FTable[Value ] ), ALL ( 'Category Table'[Category] ) )
    VAR _OtherValue =
        _ALL
            - CALCULATE ( SUM ( FTable[Value ] ), ALLSELECTED ( 'Category Table'[Category] ) )
    VAR _IfFilt =
        IF (
            ISINSCOPE ( 'Table Cat with Other'[Category] ),
            IF ( MAX ( 'Table Cat with Other'[Category] ) = "Other", _OtherValue, _Sales ),
            _ALL
        )
    VAR _NotFilt =
        IF (
            AND (
                ISINSCOPE ( 'Table Cat with Other'[Category] ),
                MAX ( 'Table Cat with Other'[Category] ) = "Other"
            ),
            BLANK (),
            _Sales
        )
    RETURN
        IF ( ISFILTERED ( 'Category Table'[Category] ), _IfFilt, _NotFilt )
    

    Next set up the page with the slicer from the Category Dimension Table, and the visual with the category field from the disconnected table and you will get:

    Sample PBIX attached

  • Check out this article by SQL BI. Although you don't need the TOPN functionality I think that their method for generating a row for Others will work in your situation too.