cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DublinOH_User
Helper III
Helper III

Power Apps: Count rows based on a second column's value while filtering of a third column

Thank you to whoever tries to help. I have not been able to figure out how to achieve the goal listed.
   GOAL: To show the percentage for each plant of their PM status type in GalleryCt  (example: see screen shot)

Stuck on #1

1. Obtain the total number of 'NAME OF PM' rows from the entire table for each distinct PlantGroup in GalleryCt

Not sure how to account for the filter after finding #1

2. Based on a filter of a third column named 'PM STATUS',   obtain the total number of 'NAME OF PM' rows for each PlantGroup per status value ("Overdue", "Next30",  "Current" **This last one comes from another table, but that I can figure out based advise from this solution)

I feel I can write the SUM and division for #3 once I can find the totals from 1 & 2

3.Once I have those totals, then we will divide the number of NAME of PM rows per status to find percentages for each group per status

 

_________________________________________________________________________________________________________________
GalleryCt ITEMS:

Sort(
GroupBy(
Filter(
'PF_PM_ Compliance_OverNext30',
'PM STATUS'.Value = "Overdue",
'SERVICE COMPLETED DATE' = Blank()
),
"PLANT_x0020_CODE",
"PlantGroup"
),
CountRows(PlantGroup),
SortOrder.Descending
)

 

This is my attempt but it is not working, but I feel it explains what I am trying to accomplish. 
Total number of  rows for 'NAME OF PM' per group

If('PLANT CODE'.Value=ThisItem.PlantGroup,CountRows('PF_PM_ Compliance_OverNext30'.'NAME OF PM'),"")

 

If('PLANT CODE'.Value=ThisItem.PlantGroup,CountRows('PF_PM_ Compliance_OverNext30'.'NAME OF PM'="Overdue"),"")

 

Additional info:

Plant group value is in the gallery  -  label named: LabelCt_PlantGrp

Gallery name: GalleryCt 
Table Source: PF_PM_ Compliance_OverNext30


Secondary Table Source* for "Current" status:  PF_PM_ Compliance_Current

*The tables are identical data sets. Divided the current status into its own SharePoint list to keep the row limit from being too large.

Thanks again to any that might reply.

1 ACCEPTED SOLUTION

Accepted Solutions
rubin_boer
Super User
Super User

hi @DublinOH_User 

 

Here is an approach that might work for you.

 

1. Group the data first without the filter for 'PM STATUS'.Value

2. Addcolumn to which you will use in the calculation

AddColumns(

GroupBy(

  Filter('PF_PM_ Compliance_OverNext30', IsBlank('SERVICE COMPLETED DATE')),
  "PLANT_x0020_CODE",
  "PlantGroup"),

"Total by Plant", CountRows(PlantGroup),

"Total Overdue", CountRows(Filter(PlantGroup,'PM STATUS'.Value = "Overdue")

)))

 

Now you have both the counts to do the % overdue.

 

Hope this helps

 
hey there if you liked the post give it a thumbs up, and if it solved your question please accept it as a solution.

View solution in original post

2 REPLIES 2
rubin_boer
Super User
Super User

hi @DublinOH_User 

 

Here is an approach that might work for you.

 

1. Group the data first without the filter for 'PM STATUS'.Value

2. Addcolumn to which you will use in the calculation

AddColumns(

GroupBy(

  Filter('PF_PM_ Compliance_OverNext30', IsBlank('SERVICE COMPLETED DATE')),
  "PLANT_x0020_CODE",
  "PlantGroup"),

"Total by Plant", CountRows(PlantGroup),

"Total Overdue", CountRows(Filter(PlantGroup,'PM STATUS'.Value = "Overdue")

)))

 

Now you have both the counts to do the % overdue.

 

Hope this helps

 
hey there if you liked the post give it a thumbs up, and if it solved your question please accept it as a solution.
DublinOH_User
Helper III
Helper III

Thank you, your help is appreciated.
Any Idea on how to show text in a label with the value of a second column, using the selected.result of a distinct drop-down list.  ??Do I need a second distinct list of LOCATION DESCRIPTION??

This is what I tried, along with several other attemps
TEXT=Distinct(Filter('PF_PM_ Compliance_OverNext30','LOCATION DESCRIPTION','PLANT CODE'=DrpdwnPlantCd_OvNx.Selected.Result))

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (5,848)