Top 5 categories month wise by the recent month

[Rami_Aziz]

hi Guys, i need your help 

im trying to create top 5 categories by monthwise, also by recent month Recent month is Nov-22

Tier1-2-3 are group of products 

as the pic below (1)

Thank you 

[AlB · Accepted solution]

@Rami_Aziz 

See it all at work in the attached file.

1. Create this measure 

_ShowMeasure = 
VAR latestMonth2_ = MAX ( 'Incident Details report'[Submitted Month2] )
VAR auxT_ =
    TOPN (
        5,
        ALL ( 'Incident Details report'[Tier 1-2-3] ),
        CALCULATE (
            [_NumIncidents],
            'Incident Details report'[Submitted Month2] = latestMonth2_,
            ALL('Incident Details report'[Submitted Month])
        )
    )
VAR tier_ = SELECTEDVALUE ( 'Incident Details report'[Tier 1-2-3] )
RETURN
    IF ( tier_ IN auxT_, 1)

that uses this:

_NumIncidents = 
COUNT ( 'Incident Details report'[Tier 1-2-3] )

2. Place [ShowMeasure] as a filter for your matrix visual. Select to show when its value is 1

Note  there's a Tier 1-2-3 value with blanks ("--") that comes up at the very top. Update the code if you want to exclude it (or remove it from the table directly)  

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[Rami_Aziz]

am trying to show it on a visual such as clustered chart , can i do that ?

each group of month separately 

[AlB]

@Rami_Aziz

See the attached file for a solution with a clustered column chart. Note I have updated the measure that determines what elements to show a bit; the previous version had some issues:

_ShowMeasure V2 =
VAR NToShow_ = 5
VAR latestMonth2_ =
    CALCULATE (
        MAX ( 'Incident Details report'[Submitted Month2] ),
        ALL ( 'Incident Details report' )
    )
VAR latestMonth_ =
    LOOKUPVALUE (
        'Incident Details report'[Submitted Month],
        'Incident Details report'[Submitted Month2], latestMonth2_
    )
VAR auxT_ =
    TOPN (
        NToShow_,
        ALL ( 'Incident Details report'[Tier 1-2-3] ),
        CALCULATE (
            [_NumberOfIncidents],
            'Incident Details report'[Submitted Month2] = latestMonth2_,
            'Incident Details report'[Submitted Month] = latestMonth_
        )
    )
VAR tier_ = SELECTEDVALUE ( 'Incident Details report'[Tier 1-2-3] )
RETURN
    IF ( tier_ IN auxT_, 1 )

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[Rami_Aziz]

Thanks AIB,

could you please share the PBIX, 

[AlB]

@Rami_Aziz 

That works.

Top 5 based on what exactly?? Who is the top 1 for instance in Nov and why?

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[Rami_Aziz]

top 5 Tier 1-2-3 count based on month of Nov, but also i need to see those top5 Tier how much they were on also oct and sep.

here on the image are the top 5 count for Nov, i also need to see Oct and Sep on the same way but the base is month of Nov, so i will be having 3 groups 

1. top 5 Tier for Nov.

2. the same top 5 from Nov catrgory but Oct count.

3. the same the same top 5 from Nov catrgory but Sep count. 

[Rami_Aziz]

Every month im receiving all the issues and categorized it into 1-2-3, so if  Nov was the last month for these top 5, how can i  also to check the same category( Top 5 from Nov) on prvious months( Oct & Sep) and if it was high on the previous month then i do have a serious issue, but if it was low then i dont need to be worried.

scenario is Top5 based on Nov  and how much they were on oct and sep, also i need to show them as visual below 

[AlB]

Hi @Rami_Aziz 

Can you share the pbix or some sample data and the expected result based on it?

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[Rami_Aziz]

Im not sure how to attach the PBIX, based on the last month which is Nov 22, i want the same products to be showen for Oct & Sep ,

so lets assume Printer-Moniter-Screen are the the top on Nov, then i need to see how much they were even in Oct & Sep.

so the top 5 from Nov, I need to see them also on Oct & Sep.

[AlB]

---

@Rami_Aziz wrote:

Im not sure how to attach the PBIX,

---

You have to share the URL to the file hosted elsewhere: Dropbox, Onedrive... or just upload the file to a site like tinyupload.com (no sign-up required).

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[Rami_Aziz]

https://1drv.ms/u/s!Ahqej0R8cjw2ixYomawSUTR-cXYT?e=9OXDGY

does that work for you ?

thank you.