Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
MV13
Frequent Visitor

TOP N filter across previous months based on current Month

Hi All

 

I am trying to create a report where the user uses a date slicer (Eg 01/01/2014 - 04/08/2015) to filter the report, I have a number of categories that I would like to display within a stacked column chart.

 

The column chart will have the X axis as the dates. I would like to filter this chart by the TOP 10 categories (By Value)  in the latest period (So if the last date is 12/01/2017, the latest period would be January 2017). So in essence the previous months must show the same top 10 as the latest month (i.e I would like to see how the top 10 in the latest month performed over previous months).

 

The table below is a high level example of the data, where Value is summed across categories 

Category

Value

Date

Category 1

0.2

11/01/2018

Category 2

1

03/02/2017

Category 3

0.3

05/05/2013

Category N

0.5

06/04/2014

 

I hope I could describe my issue clearly. Thanks in advance

1 ACCEPTED SOLUTION

Hi @MV13

 

Here's something I mocked up earlier in the day and just got home to post.

PBIX link

 

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:

  1. 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.
  2. Determine the top 10 Categories in that month (stored in variable TopCategories)
  3. 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 🙂


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

10 REPLIES 10
v-xjiin-msft
Solution Sage
Solution Sage

Hi @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 sampleI 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.

 

Capture.PNG

 

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. 

 

CategoryValueDatePlantArea
Category 10.512/01/2016Plant 1A1
Category 20.412/05/2016Plant 1A2
Category 20.512/05/2016Plant 2A3
Category 30.612/21/2016Plant 1A1
Category 40.112/28/2016Plant 1A3
Category 50.812/18/2016Plant 1A1
Category 10.21/2/2017Plant 1A2
Category 20.31/15/2017Plant 1A4
Category 30.11/12/2017Plant 1A2
Category 4 0.81/19/2017Plant 1A1
Category 50.11/25/2017Plant 1A3

 

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

 

Hi @MV13

 

Here's something I mocked up earlier in the day and just got home to post.

PBIX link

 

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:

  1. 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.
  2. Determine the top 10 Categories in that month (stored in variable TopCategories)
  3. 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 🙂


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Hi @OwenAuger 

Thanks. It helped me a lot but I have another question.
How to create a Bottom N like your solution?

 

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Hi @OwenAuger 

 

Could you please share the PBIX file instead of link so that I can see the solution as I am facing same problem.

Hi @OwenAuger Is it possible for you to share PBIX file here instead of link. I need to review your file as I am not able to understand how have you calculated 'Max Date Option'.

I am stuck with same problem.

Thanks in advance

Hi @SK87 

Sorry about the broken link! A change in my email address invalidated some of my older OneDrive links.

 

I have updated the link in the post above and also attached the file directly to that post.

 

Let me know if you have any issues. If you come across any of my other links that are not working, you can replace

owenauger_ozerconsulting_onmicrosoft_com

with

owen_owenaugerbi_com

Regards,

Owen 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Hi @OwenAuger 

Thanks a lot for the solution. It helped me. I was struggling to resolve this problem for some days. But now I'm having a second problem. I am using this example to filter the top 10 also  based on the amount... but I want to show the % variation month by month, and when I change this part:

 

// Return Value Sum filtered to those Categories

RETURN CALCULATE ( [Value Sum], KEEPFILTERS ( TopCategories ) )

to this:

// Return Value Sum filtered to those Categories

RETURN CALCULATE ( [% variation month by month], KEEPFILTERS ( TopCategories ) )

 

the measure lose it self and go back to show all the categories.

Here is my [% variation month by month] calculation:

 

VAR vValueLastMonth =
CALCULATE(
    [Value Sum]
    ,PARALLELPERIOD( DIMDATA[Data], -1, MONTH )
)

VAR vVariation =
DIVIDE(
    [Value Sum]
    ,vValueLastMonth
)

RETURN
vVariation - 1
 
 
 
What am I doing wrong?
Thanks in advance.
Raoni
Frequent Visitor

Hi @OwenAuger,

I have a third problem.

I'm trying to use those measures inside a Field Parameter, that will switch between TopN based on [Value Sum] from current month (like your explanation) and [Value Sum] of all data, so I can choose with a field parameter, which one I prefer to see on the chart.

I did some changes and it is like this:

 

// Get Top 10 Categories in that month
VAR TopCategories =
    CALCULATETABLE (
        TOPN ( 10, ALL ( Data[Category] ), ALL ( 'Calendar Date'[Date] ) ) )
    )

 

But it is filtering in a weird way, because there is not all categories, but there are more than 10. I think it is choosing the Top 10 from each month, and not from all data.

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors