topic Re: Top 5 categories month wise by the recent month in DAX Commands and Tips

Top 5 categories month wise by the recent month

Rami_Aziz — Sun, 25 Dec 2022 14:11:30 GMT

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

Re: Top 5 categories month wise by the recent month

AlB — Sun, 25 Dec 2022 20:37:52 GMT

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.

Re: Top 5 categories month wise by the recent month

Rami_Aziz — Mon, 26 Dec 2022 09:16:56 GMT

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.

Re: Top 5 categories month wise by the recent month

AlB — Mon, 26 Dec 2022 09:35:52 GMT

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.

Re: Top 5 categories month wise by the recent month

Rami_Aziz — Mon, 26 Dec 2022 09:53:29 GMT

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

does that work for you ?

thank you.

Re: Top 5 categories month wise by the recent month

AlB — Mon, 26 Dec 2022 10:08:08 GMT

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.

Re: Top 5 categories month wise by the recent month

Rami_Aziz — Mon, 26 Dec 2022 10:26:18 GMT

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

Re: Top 5 categories month wise by the recent month

Rami_Aziz — Mon, 26 Dec 2022 11:11:56 GMT

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.

Re: Top 5 categories month wise by the recent month

AlB — Mon, 26 Dec 2022 11:24:14 GMT

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.

Re: Top 5 categories month wise by the recent month

Rami_Aziz — Mon, 26 Dec 2022 11:26:34 GMT

Thanks AIB,

could you please share the PBIX,

Re: Top 5 categories month wise by the recent month

Rami_Aziz — Mon, 26 Dec 2022 11:41:50 GMT

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

each group of month separately

Re: Top 5 categories month wise by the recent month

AlB — Wed, 28 Dec 2022 16:50:29 GMT

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.