Forum Discussion
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 Prio...
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
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 -
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
You are a Rockstar 🙂
Thankyou so much