Forum Discussion

Suzy123's avatar
Suzy123
New Member
3 years ago
Solved

Top N and Others

Hi all, 

 

I am doing a top 5 plus others for my report, and it works fine until i put the month in the columns it will show top 5 for each month and Total column will only add the Top 5 based on the total result. 

 

The measure I created is as below, and this is how it looks before I put the Month in the Columns:

 

 

This is how it looks after I put the Month in the columns:

 

Does anyone has any idea how to resolve this... it seems so hard, and the result I want is just 

 

 

Mesure:

Top N Sum Sales = 
VAR TopSalesTable =
    TOPN (
        5,
        ALLSELECTED ('Unique Customers'),
        [Total Sales]
    )
VAR TopSales =
    CALCULATE (
        [Total Sales],
        KEEPFILTERS ( TopSalesTable )
    )
VAR OtherSales =
    CALCULATE (                  
        [Total Sales],
        ALLSELECTED ('Unique Customers' )
    )
        - CALCULATE (                        
            [Total Sales],
            TopSalesTable
        )
VAR CurrentProd = SELECTEDVALUE ( 'Unique Customers'[Customer name] )
RETURN
    IF (                                     
        CurrentProd <> "Others",
        TopSales,
        OtherSales
    )

 

  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

    I tried to simplify the sample and created a new sample pbix file like below. (top3 and others)

    Please check the below picture and the attached pbix file.

    I hope the below can provide some ideas on how to create a solution for your data model.

     

     

    Top 3 sales and others: =
VAR _topthreecustomerlistwholeyear =
    TOPN (
        3,
        ALLSELECTED ( Customer[Customer] ),
        CALCULATE ( [Sales total:], REMOVEFILTERS ( 'Calendar' ) ), DESC
    )
VAR _topthreefilter =
    TOPN ( 3, ALLSELECTED ( Customer[Customer] ), [Sales total:], DESC )
VAR _allsales =
    CALCULATE ( [Sales total:], REMOVEFILTERS ( Customer ) )
VAR _othersales =
    _allsales - CALCULATE ( [Sales total:], _topthreefilter )
RETURN
    SWITCH (
        TRUE (),
        HASONEVALUE ( Customer[Customer] ),
            IF (
                SELECTEDVALUE ( Customer[Customer] ) = "Others",
                _othersales,
                CALCULATE (
                    [Sales total:],
                    KEEPFILTERS ( Customer[Customer] IN _topthreecustomerlistwholeyear )
                )
            ),
        [Sales total:]
    )

     

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    3 years ago

    Hi,

    I tried to simplify the sample and created a new sample pbix file like below. (top3 and others)

    Please check the below picture and the attached pbix file.

    I hope the below can provide some ideas on how to create a solution for your data model.

     

     

    Top 3 sales and others: =
VAR _topthreecustomerlistwholeyear =
    TOPN (
        3,
        ALLSELECTED ( Customer[Customer] ),
        CALCULATE ( [Sales total:], REMOVEFILTERS ( 'Calendar' ) ), DESC
    )
VAR _topthreefilter =
    TOPN ( 3, ALLSELECTED ( Customer[Customer] ), [Sales total:], DESC )
VAR _allsales =
    CALCULATE ( [Sales total:], REMOVEFILTERS ( Customer ) )
VAR _othersales =
    _allsales - CALCULATE ( [Sales total:], _topthreefilter )
RETURN
    SWITCH (
        TRUE (),
        HASONEVALUE ( Customer[Customer] ),
            IF (
                SELECTEDVALUE ( Customer[Customer] ) = "Others",
                _othersales,
                CALCULATE (
                    [Sales total:],
                    KEEPFILTERS ( Customer[Customer] IN _topthreecustomerlistwholeyear )
                )
            ),
        [Sales total:]
    )

     

    • Suzy123's avatar
      Suzy123
      New Member
      3 years ago

      Appreciate your help!! Genius! It works now 🙂