Forum Discussion
akashpadmakumar
4 years agoHelper I
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...
- 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
PaulDBrown
4 years agoCommunity Champion
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
4 years agoHelper I
You are a Rockstar 🙂
Thankyou so much