cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
matiellu2
New Member

Calculate rows of distinct groups that have more than "5 requests"

Hello dear.
I have a doubt in calculating the number of lines for Groups that have more than 5 Requests. In this case, I have an indicator that the total base is the number of distinct groups and of which there are more than 5 requests.
Example:

Group A: 2 Requests
Group B: 7 Requests
Group C: 6 Requests
Group 😧 1 Request

 

So, there are 4 groups and only two of them have more than 5 requests (>5).

So my indicator (speedometer) should show 50%.

---------------------------------------------------------------------------------------------------------

 

So far these are the calculations and commands I created, but they are not working:

 

1.) I did the calculation first to count the lines with a filter:

matiellu2_8-1659969626983.png

----------------------------------------------------------------------------------------------------------

2.) Then I calculated this row calculation to show which row has more than 5

- I think this is the problem with the calculation

matiellu2_4-1659969293168.png

matiellu2_7-1659969547595.png

---------------------------------------------------------------------------------------------

3.) Here's just the distinct groups I need, that's right

matiellu2_5-1659969399778.png

 

---------------------------------------------------------------------------------------------

Next is shown in the 25% indicator, it is incorrect. It should show 0%, since none of the groups presented more than 5 requests:

matiellu2_2-1659969191388.png

matiellu2_6-1659969467303.png

 

Could you help me with this calculation?

 

Thanks

 

1 ACCEPTED SOLUTION
Ethan96
Resolver I
Resolver I

Hi, @matiellu2 

Try the codes below:

Count = DISTINCTCOUNT('Table'[Status])
Count>5 = 
var tab=SUMMARIZE('Table','Table'[Status],"Count",COUNTROWS('Table'))
return CALCULATE(DISTINCTCOUNT('Table'[Status]),FILTER(tab,[Count]>5))
Percentage = [Count>5]/[Count] 

Ethan96_0-1660213825553.png

Sample pbix

 

Regards,

Ethan

View solution in original post

1 REPLY 1
Ethan96
Resolver I
Resolver I

Hi, @matiellu2 

Try the codes below:

Count = DISTINCTCOUNT('Table'[Status])
Count>5 = 
var tab=SUMMARIZE('Table','Table'[Status],"Count",COUNTROWS('Table'))
return CALCULATE(DISTINCTCOUNT('Table'[Status]),FILTER(tab,[Count]>5))
Percentage = [Count>5]/[Count] 

Ethan96_0-1660213825553.png

Sample pbix

 

Regards,

Ethan

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.