Forum Discussion
TOP N filter across previous months based on current Month
Hi MV13
Here's something I mocked up earlier in the day and just got home to post.
I set up a basic data model with a Calendar table and a Data table, with Data containing columns Date, Category, Value.
To calculate the sum of Value in any time period for the top 10 Categories (determined in the latest month), the measure I wrote has 3 parts:
- Define the time period where the top 10 Categories will be determined (the 'max' month in this case)
I added a parameter to choose between the max date filtered & the max date present in the data.
Regardless, the max date is expanded out to a calendar month (in variable MaxMonth), and this time period is used to determine the top 10 Categories. - Determine the top 10 Categories in that month (stored in variable TopCategories)
- Calculate sum of Value filtered to those categories.
Here is the actual measure (colour-coding matches above):
Value Sum For Top 10 Categories in Max Month = // Get Relative or Absolute selection VAR MaxDateOption = SELECTEDVALUE ( 'Max Date Option'[Max Date Option] )
// Get Max Date VAR MaxDate = SWITCH ( MaxDateOption, "Latest Data", CALCULATE ( MAX ( Data[Date] ), ALL ( Data ) ), "Current Date Filter", CALCULATE ( MAX ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar' ) ) ) // Expand this to a calendar month VAR MaxMonth = CALCULATETABLE ( PARALLELPERIOD ( 'Calendar'[Date], 0, MONTH ), 'Calendar'[Date] = MaxDate ) // Get Top 10 Categories in that month VAR TopCategories = CALCULATETABLE ( TOPN ( 10, ALL ( Data[Category] ), CALCULATE ( [Value Sum], MaxMonth ) ) ) // Return Value Sum filtered to those Categories RETURN CALCULATE ( [Value Sum], KEEPFILTERS ( TopCategories ) )Well, that's how I would approach it.
The part in red can be adjusted to whatever method you want to use to determine the 'max' month.
Regards,
Owen 🙂
- Define the time period where the top 10 Categories will be determined (the 'max' month in this case)
Solution SageHi MV13,
To achieve your requirement, please refer to following method:
1. create a new column called YearMonth to make groups:
YearMonth = YEAR(Test[Date])*100 + MONTH(Test[Date])
2. create a new column to make sequence for the values in each YearMonth group:
Rank =
RANKX (
FILTER ( Test, Test[YearMonth] = EARLIER ( Test[YearMonth] ) ),
Test[Value],
,
ASC,
DENSE
)3. Then you can create a new calculate table to get the top 10 values of each YearMonth group based on the Rank column:
TopN = FILTER(Test,Test[Rank]<=3)
I selected top 3 in my sample
4. Use this new calculated table to create the column chart.
If above method doesn't satisfy your requirement. Please share us more information like some original sample data which we can copy and paste directly and its expected result. So that we can get a right direction and make some proper tests.
Thanks,
Xi Jin.
Hi v-xjiin-msft
Thank you for your response, I have tried your solution based on the same data that you have used. From what I understand this gives me the top N for each month, this is useful however, what I am trying to visualise is how the top N in the latest period has performed over previous periods, so in this example, the top N for January 2018 is Category 1,2,3 and 5. So in my visual, I should only see categories 1,2,3 and 5 across the previous months, i.e Category 4 should not appear in December 2017.
Note: For any given month, a category can appear multiple times, I have a total of 300+ categories. In addition to date, I am slicing this date by the Plant (Factory) that it comes from, as well as the Area within the factory.
| Category | Value | Date | Plant | Area |
| Category 1 | 0.5 | 12/01/2016 | Plant 1 | A1 |
| Category 2 | 0.4 | 12/05/2016 | Plant 1 | A2 |
| Category 2 | 0.5 | 12/05/2016 | Plant 2 | A3 |
| Category 3 | 0.6 | 12/21/2016 | Plant 1 | A1 |
| Category 4 | 0.1 | 12/28/2016 | Plant 1 | A3 |
| Category 5 | 0.8 | 12/18/2016 | Plant 1 | A1 |
| Category 1 | 0.2 | 1/2/2017 | Plant 1 | A2 |
| Category 2 | 0.3 | 1/15/2017 | Plant 1 | A4 |
| Category 3 | 0.1 | 1/12/2017 | Plant 1 | A2 |
| Category 4 | 0.8 | 1/19/2017 | Plant 1 | A1 |
| Category 5 | 0.1 | 1/25/2017 | Plant 1 | A3 |
I will try to dummy up some of the actual data so that it is easier for you to understand my issue. For some context, I am looking at the duration of downtimes in a plant(Value) and the cause of the downtime (Category).
Thanks
- OwenAuger
Super User
Hi MV13
Here's something I mocked up earlier in the day and just got home to post.
I set up a basic data model with a Calendar table and a Data table, with Data containing columns Date, Category, Value.
To calculate the sum of Value in any time period for the top 10 Categories (determined in the latest month), the measure I wrote has 3 parts:
- Define the time period where the top 10 Categories will be determined (the 'max' month in this case)
I added a parameter to choose between the max date filtered & the max date present in the data.
Regardless, the max date is expanded out to a calendar month (in variable MaxMonth), and this time period is used to determine the top 10 Categories. - Determine the top 10 Categories in that month (stored in variable TopCategories)
- Calculate sum of Value filtered to those categories.
Here is the actual measure (colour-coding matches above):
Value Sum For Top 10 Categories in Max Month = // Get Relative or Absolute selection VAR MaxDateOption = SELECTEDVALUE ( 'Max Date Option'[Max Date Option] )
// Get Max Date VAR MaxDate = SWITCH ( MaxDateOption, "Latest Data", CALCULATE ( MAX ( Data[Date] ), ALL ( Data ) ), "Current Date Filter", CALCULATE ( MAX ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar' ) ) ) // Expand this to a calendar month VAR MaxMonth = CALCULATETABLE ( PARALLELPERIOD ( 'Calendar'[Date], 0, MONTH ), 'Calendar'[Date] = MaxDate ) // Get Top 10 Categories in that month VAR TopCategories = CALCULATETABLE ( TOPN ( 10, ALL ( Data[Category] ), CALCULATE ( [Value Sum], MaxMonth ) ) ) // Return Value Sum filtered to those Categories RETURN CALCULATE ( [Value Sum], KEEPFILTERS ( TopCategories ) )Well, that's how I would approach it.
The part in red can be adjusted to whatever method you want to use to determine the 'max' month.
Regards,
Owen 🙂
Hi @OwenAuger
Thanks. It helped me a lot but I have another question.
How to create a Bottom N like your solution?- OwenAuger
Hi sarochch
TOPN can also be used to return the "bottom N" items, by including an Order argument equal to ASC.
In the example above, it would be the 4th argument of TOPN:
TOPN ( 10, ALL ( Data[Category] ), CALCULATE ( [Value Sum], MaxMonth ), ASC )
If the Order argument = DESC (default), values are sorted largest to smallest.
If the Order argument = ASC, values are sorted smallest to largest.
Regards,
Owen
- Define the time period where the top 10 Categories will be determined (the 'max' month in this case)
