Top N and Remaining using dynamic filter

[akashpadmakumar]

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

[akashpadmakumar]

Please see the sample Data for Reference -

Reporting Currency Amount	Reporting Period	Customer ID	Customer Rank
$2,500,467.75	Current Period	CUSTAA01	1
$2,472,102.16	Current Period	CUSTAA02	2
$2,204,039.66	Current Period	CUSTAA03	3
$2,135,451.46	Prior Period	CUSTAA04	4
$2,046,299.22	Prior Period	CUSTAA05	5
$1,813,237.68	Current Period	CUSTAA06	6
$1,782,454.35	Prior Period	CUSTAA07	7
$1,668,593.06	Current Period	CUSTAA08	8
$1,545,152.72	Current Period	CUSTAA09	9
$1,528,924.96	Prior Period	CUSTAA10	10

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

Output expected -

[PaulDBrown · Accepted solution]

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

[akashpadmakumar]

You are a Rockstar 🙂
Thankyou so much

[amitchandak]

@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/3...

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/50...
https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slic...
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]

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