Forum Discussion

akashpadmakumar's avatar
akashpadmakumar
Helper I
4 years ago
Solved

Top N and Remaining using dynamic filter

Hi Everyone,

I have a TOP N Filter which is dynamic and and has values 1-10

I need to have 4 visuals as cards - 

 

1) Top N Prior Period Revenue

2) Top N Current Period Revenue

3) Others Prior Period Revenue

4) Others Current Period Revenue

 

The available data is Customer ID, Reporting Period, Amount
The top N is based on amount and also the reporting period. After the Top N, remaining is Others and shown in 2 cards according to reporting period.

 

I have searched , wasnt able to find a solution for specifically getting the values in cards with repect to another column.

 

Kindly help

Need Help
  • PaulDBrown's avatar
    PaulDBrown
    4 years ago

    See if this works for you. 

    Create the measure for the revenue for each period:

     

    Revenue Current Period =
CALCULATE (
    SUM ( 'Table'[Reporting Currency Amount] ),
    FILTER ( 'Table', 'Table'[Reporting Period] = "Current Period" )
)

    Revenue Prior Period =
CALCULATE (
    SUM ( 'Table'[Reporting Currency Amount] ),
    FILTER ( 'Table', 'Table'[Reporting Period] = "Prior Period" )
)

     

    Create the RANKs for each period:

    RANK by Revenue for Current Period =
IF (
    ISBLANK ( [Revenue Current Period] ),
    BLANK (),
    RANKX ( ALL ( 'Table' ), [Revenue Current Period] )
)

    RANK by Revenue for Prior Period =
IF (
    ISBLANK ( [Revenue Prior Period] ),
    BLANK (),
    RANKX ( ALL ( 'Table' ), [Revenue Prior Period] )
)

    I have this table to use as the dynamic TopN silcer:

     

    Now create the TopN & other measures:

    Current Period TopN = 
VAR MX = MAX('TopN'[TopN])
RETURN
CALCULATE([Revenue Current Period], FILTER('Table', [RANK by Revenue for Current Period] <= MX))
    Current Period Others = 
[Revenue Current Period] - [Current Period TopN]

    and 

    Prior Period TopN =
VAR MX =
    MAX ( 'TopN'[TopN] )
RETURN
    CALCULATE (
        [Revenue Prior Period],
        FILTER ( 'Table', [RANK by Revenue for Prior Period] <= MX )
    )

    Prior Period Others = 
[Revenue Prior Period] - [Prior Period TopN]

     

    To get

    I've attached the sample PBIX

     

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    akashpadmakumar ,
    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

     

    but time intelligence and what if parameters can help

     

    measure like

    MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
    last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

     

    TOPN example

    CALCULATE([MTD Sales],TOPN([What if value],allselected(Date[Month year]),[MTD Sales],DESC),VALUES(Date[Month year]))

     

     

    refer

    https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415

     

    Also, check the calculation group and measure slicers

    measure slicer
    https://www.youtube.com/watch?v=b9352Vxuj-M
    https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/500115
    https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slicer-parameter-table-pattern
    https://www.youtube.com/watch?v=vlnx7QUVYME

    calculation groups
    https://www.sqlbi.com/blog/marco/2020/07/15/creating-calculation-groups-in-power-bi-desktop/

    • akashpadmakumar's avatar
      akashpadmakumar
      Helper I
      4 years ago

      Here the issue is, Reporting period is not Date

      It has only 2 values - Current Period, Prior Period

      Customer ID has unique values

      Amount has Amount in Dollars

       

      So the Top N is based on Amount and the Others is the remaining for both the Prior and Current Period are the 4 cards I need

       

      Output needed

       

  • akashpadmakumar's avatar
    akashpadmakumar
    Helper I
    4 years ago

    Please see the sample Data for Reference -

     

    Reporting Currency AmountReporting PeriodCustomer IDCustomer Rank
    $2,500,467.75Current PeriodCUSTAA011
    $2,472,102.16Current PeriodCUSTAA022
    $2,204,039.66Current PeriodCUSTAA033
    $2,135,451.46Prior PeriodCUSTAA044
    $2,046,299.22Prior PeriodCUSTAA055
    $1,813,237.68Current PeriodCUSTAA066
    $1,782,454.35Prior PeriodCUSTAA077
    $1,668,593.06Current PeriodCUSTAA088
    $1,545,152.72Current PeriodCUSTAA099
    $1,528,924.96Prior PeriodCUSTAA1010

     

    I have a filter Top N Customers with values from 1-10

     

    Output expected -

     

    • PaulDBrown's avatar
      PaulDBrown
      Community Champion
      4 years ago

      See if this works for you. 

      Create the measure for the revenue for each period:

       

      Revenue Current Period =
CALCULATE (
    SUM ( 'Table'[Reporting Currency Amount] ),
    FILTER ( 'Table', 'Table'[Reporting Period] = "Current Period" )
)

      Revenue Prior Period =
CALCULATE (
    SUM ( 'Table'[Reporting Currency Amount] ),
    FILTER ( 'Table', 'Table'[Reporting Period] = "Prior Period" )
)

       

      Create the RANKs for each period:

      RANK by Revenue for Current Period =
IF (
    ISBLANK ( [Revenue Current Period] ),
    BLANK (),
    RANKX ( ALL ( 'Table' ), [Revenue Current Period] )
)

      RANK by Revenue for Prior Period =
IF (
    ISBLANK ( [Revenue Prior Period] ),
    BLANK (),
    RANKX ( ALL ( 'Table' ), [Revenue Prior Period] )
)

      I have this table to use as the dynamic TopN silcer:

       

      Now create the TopN & other measures:

      Current Period TopN = 
VAR MX = MAX('TopN'[TopN])
RETURN
CALCULATE([Revenue Current Period], FILTER('Table', [RANK by Revenue for Current Period] <= MX))
      Current Period Others = 
[Revenue Current Period] - [Current Period TopN]

      and 

      Prior Period TopN =
VAR MX =
    MAX ( 'TopN'[TopN] )
RETURN
    CALCULATE (
        [Revenue Prior Period],
        FILTER ( 'Table', [RANK by Revenue for Prior Period] <= MX )
    )

      Prior Period Others = 
[Revenue Prior Period] - [Prior Period TopN]

       

      To get

      I've attached the sample PBIX