Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
Solved! Go to Solution.
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. |
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. |
am trying to show it on a visual such as clustered chart , can i do that ?
each group of month separately
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. |
Thanks AIB,
could you please share the PBIX,
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. |
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
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. |
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).
|
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. |
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
26 | |
20 | |
19 | |
14 | |
13 |
User | Count |
---|---|
43 | |
36 | |
24 | |
23 | |
21 |