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

Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more

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
Community Champion
Community Champion

@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
Community Champion
Community Champion

@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 

 

AlB
Community Champion
Community Champion

@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
Community Champion
Community Champion

@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
Community Champion
Community Champion

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.

AlB
Community Champion
Community Champion


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

Power BI Monthly Update - April 2025

Check out the April 2025 Power BI update to learn about new features.

Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

April2025 Carousel

Fabric Community Update - April 2025

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