Forum Discussion

icdns's avatar
icdns
Post Patron
4 years ago
Solved

Dynamic based on filters selected: Top 5 and Others (RANK)

Hi everyone,    Would like to ask for your help as I have a list of Categories and I want to group them by Top 5 and the rest will be grouped as "Others" and as I filter my dimension the Ranking of...
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    Thank you for your message.

    Could you please check the below and the attached pbix file, whether it suits your requirement?

     

     

    New Table 2 =
VAR _topNnumber = 5
VAR _totalsales =
    SUM ( Sales2[Sales] )
VAR _topfivesales =
    CALCULATE (
        SUM ( Sales2[Sales] ),
        TOPN (
            5,
            SUMMARIZE ( Sales2, Sales2[Category] ),
            CALCULATE ( SUM ( Sales2[Sales] ) ), DESC
        )
    )
VAR _topfivetable =
    ADDCOLUMNS (
        TOPN (
            _topNnumber,
            SUMMARIZE ( Sales2, Sales2[Category] ),
            CALCULATE ( SUM ( Sales2[Sales] ) ), DESC
        ),
        "@Sales", CALCULATE ( SUM ( Sales2[Sales] ) ),
        "@Ranking",
            RANKX (
                SUMMARIZE ( Sales2, Sales2[Category] ),
                CALCULATE ( SUM ( Sales2[Sales] ) ),
                ,
                DESC
            ),
        "@contribution", FORMAT ( CALCULATE ( SUM ( Sales2[Sales] ) ) / SUM ( Sales2[Sales] ), "#0.00%" )
    )
VAR _otherstable =
    {
        ( "Others", _totalsales - _topfivesales, _topNnumber + 1, FORMAT ( ( _totalsales - _topfivesales ) / _totalsales, "#0.00%" ) )
    }
RETURN
    UNION ( _topfivetable, _otherstable )
Jihwan_Kim's avatar
Jihwan_Kim
Super User
4 years ago

Hi,

Please check the below picture and the attached pbix file.

It is for creating a new table.

 

 

New Table = 
VAR _totalsales =
    SUM ( Sales[Sales] )
VAR _topfivesales =
    CALCULATE (
        SUM ( Sales[Sales] ),
        TOPN ( 5, Sales, CALCULATE ( SUM ( Sales[Sales] ) ), DESC )
    )
VAR _topfivetable =
    ADDCOLUMNS (
        TOPN ( 5, Sales, CALCULATE ( SUM ( Sales[Sales] ) ), DESC ),
        "@Ranking", RANKX ( Sales, CALCULATE ( SUM ( Sales[Sales] ) ),, DESC ),
        "@contribution", FORMAT ( CALCULATE ( SUM ( Sales[Sales] ) ) / SUM ( Sales[Sales] ), "#0.00%" )
    )
VAR _otherstable =
    {
        ( "Others", _totalsales - _topfivesales, "otherranking", FORMAT ( ( _totalsales - _topfivesales ) / _totalsales, "#0.00%" ) )
    }
RETURN
    UNION ( _topfivetable, _otherstable )
  • icdns's avatar
    icdns
    Post Patron
    4 years ago

    Hi Jihwan_Kim ,

     

    I tried your formula but I am getting this error..

    "Each table argument of 'UNION' must have the same number of columns."

     

    😞

    • Jihwan_Kim's avatar
      Jihwan_Kim
      Super User
      4 years ago

      Hi,

      I think your sales table has more columns than what I have in my sample pbix file.

      Please share your sample pbix file's link, and then I can try to look into it to come up with a more relevant solution for your dataset.

      Thanks.