Answered

Percentage of UserV from choice field if User is Active from another field

(0) Share

Report

Posted on by GoGorilla

160

Using a SharePoint list as my connection, I need to either get the counts of UserV if User = V AND count of UserStatus if Status = Active so that I can then get the percentage of User V in the Active group. I have both in separate collections and figured out that I can't do a CountIf with conditional formatting on two collections (or according to what I have tried), e.g. CountIf( collUserV, UserV.Value = "V" && collUserStatus, Status.Value = "Active".

Does anyone have a simpler way to achieve this? I tried to do a CountIf with a Filter, but wasn't able to do it with two collections. I am trying to avoid delegation issues even though I will not exceed 2,000 items.

Many thanks in advance!

Categories:

Building Power Apps

All responses (4)

Answers (1)

Sort by

RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@GoGorilla

If your list is not going to exceed 2000, then don't worry about the delegation warning. It does not impact your app.

I am not sure what you mean by "I have both in separate collections" - both of what?

This will give you a count based on the conditions you mentioned (no collection in use here):

CountRows(Filter(yourSPList, UserV.Value = "V" && UserStatus.Value="Active"))

I hope this is helpful for you.
Verified answer

v-qiaqi@microsoft.com Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi@GoGorilla,

Could you please tell me that both of the User and Status are Choice column types?

Do you want to get the percentage of user V in the Active group?

I have a test on my side, please take a try as below.

Concatenate( Text( Round( Value( CountIf( UserStatus, User.Value = "V" && Status.Value = "Active" ) / CountIf( UserStatus, Status.Value = "Active" ) ) * 100, 0 ), "[$-en-US]0" ), "%" )

Note: I have a SP list named 'UserStatus' and both of the User and Status are Choice column types.

Hope it could help you.

Regards,

Qi
GoGorilla 160 on at

Like
a
(0)

Report
Copy link

Link copied!
@v-qiaqi-msft @RandyHayes I have two different scenarios. One scenario, the fields are both choice fields. The formula works great. The other is a boolean and choice, which should be fine but the count of rows for my variable in PowerApps does not match the count of rows in the SharePoint list. This is my true code (I am not the person who created these fields with the spaces between, FYI):
Set( varMilSpouseNum, CountRows(Filter(EmpDB, 'Active Service Spouse'= true && 'EE Status'.Value = "Active")) );
I have it set to refresh the connection on Submit. It does change the number in both places but they still do not match. I did change the field type from text to Y/N and removed the old data but it still should work.
GoGorilla 160 on at

Like
a
(0)

Report
Copy link

Link copied!

@v-qiaqi-msft @RandyHayes I noted that my formula was wrong above where 'Active Service Spouse', true is the correct syntax. I removed some of my old data and restarted my computer. It finally started working properly. It was counting false items but corrected itself. Thank you both!