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
Rami_Aziz
Helper I
Helper I

Top 5 categories month wise by the recent month

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)

 

 

Rami_Aziz_1-1671976182080.png

 

 

 

Rami_Aziz_0-1671976048039.png

Thank you 

1 ACCEPTED SOLUTION
AlB
Super User
Super User

@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)  

 


SU18_powerbi_badge

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.

 

 

View solution in original post

11 REPLIES 11
AlB
Super User
Super User

@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)  

 


SU18_powerbi_badge

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.

 

 

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

each group of month separately 

 

@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 )

 

SU18_powerbi_badge

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.

 

Thanks AIB,

could you please share the PBIX, 

AlB
Super User
Super User

@Rami_Aziz 

That works.

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

 

SU18_powerbi_badge

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.

 

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. 

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 

  

Rami_Aziz_1-1672050174348.png

Rami_Aziz_0-1672049975953.png

 

 

 

 

 

AlB
Super User
Super User

Hi @Rami_Aziz 

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

 

SU18_powerbi_badge

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.

 

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.


@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).

 

SU18_powerbi_badge

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.

 

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

 

does that work for you ?

 

thank you.

 

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.