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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors