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
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors