Forum Discussion
Top N and Remaining using dynamic filter
- 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
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
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/
- akashpadmakumar4 years agoHelper I
Here the issue is, Reporting period is not Date
It has only 2 values - Current Period, Prior PeriodCustomer 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