cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
BrendonBrooksP1 Post Patron
Post Patron

Sum and Filter with If Function

Good day. I have a custom report that shows how many various flights our crew members have done over the course of the year.  This data is shown with a Label. 

To show this data, I've created few labels and a drop-down menu to select the crew member. Upon selection of the crew member, the data will show how many flight hours, miles and total flights that employee has done in the year.  To verify that PowerApps is displaying the correct information, I have an Excel spreadsheet with a series of Pivot tables.  Here is the information that I’m required to have:

  1. Total Flights
  2. Total Hours
  3. Total Mileage
  4. Total Medevac Flights
  5. Total Training Flights
  6. Total Medevac Hours
  7. Total Training Hours. 

Each crew member has different a different role during a flight. Some are assigned to Crew Member 1, some are assigned to Crew Member 2, some are assigned to Crew Member 3.  There are a few employees who can only be Crew Member 1. The same goes for Crew Member 2 and 3, which are the same job.

 

What Im having trouble with, is creating a formula that searches the table and gives me the correct amount of flights, hours and miles. Here is what I have so far:

 

Total flights:

CountRows(Filter('[dbo].[CmStats]', Cm1 = StatDropDown.Selected.Value && Cm2 = StatDropDown.Selected.Value && Cm3 = StatDropDown.Selected.Value))

 

Total hours:

Sum(Filter(ytdGal.AllItems, Cm1=StatDropDown.Selected.Value && Cm2=StatDropDown.Selected.Value && Cm3=StatDropDown.Selected.Value),FlightTime)

 

Total miles:

Sum(Filter(ytdGal.AllItems, Cm1=StatDropDown.Selected.Value && Cm2=StatDropDown.Selected.Value && Cm3=StatDropDown.Selected.Value),Mileage)

 

Total Flights of a specific flight: 

CountRows(Filter('[dbo].[CmStats]', Cm1 = Cmdrop.Selected.Value,Cm2 = Cmdrop.Selected.Value, Cm3=Cmdrop.Selected.Value, MissionType = "Medevac"))

 

These formulas will display incorrect data when compared to my Excel spreadsheet. Is there a way that I can use an IF statement to better my formula?

 

v/r

Brendon

9 REPLIES 9
BitLord69 Power Participant
Power Participant

Re: Sum and Filter with If Function

Where do you get the wrong result, in all of the Countrows nad Sums or just some of them?

 

BrendonBrooksP1 Post Patron
Post Patron

Re: Sum and Filter with If Function

The Sum function is where I'm getting a lot of incorrect data.  

 

I think it's due to it taking Cm (crew member) columns into one whole argument instead of individual ones. 

BitLord69 Power Participant
Power Participant

Re: Sum and Filter with If Function

Do you still have the problem? I had the same thing going on, Sum using a Filter. It worked for a long while, then suddenly stopped. I reported the bug and now it works again. Hope it's the same for you!

BrendonBrooksP1 Post Patron
Post Patron

Re: Sum and Filter with If Function

@BitLord69I still have it, but I haven't looked at the issue for a few days. I will plan on doing it next week.  Were you able to filter multiple columns?

BitLord69 Power Participant
Power Participant

Re: Sum and Filter with If Function

@BrendonBrooksP1 I only filter on a single column. Here's the code I use, if that's any help at all:

 

Text(Coalesce(Sum(Filter('[dbo].[NyOrderRad]', OrderhuvudId = ThisItem.Id), Summa),0), "[$-sv-SE]# ### kr")

Will you get results if you sort only on a single column? What if you work directly on the table and not the gallery?

BrendonBrooksP1 Post Patron
Post Patron

Re: Sum and Filter with If Function

Thanks for the code! I'll give it a shot!

BrendonBrooksP1 Post Patron
Post Patron

Re: Sum and Filter with If Function

Instead of playing around with the Sum If idea, instead I decided to improve my gallery filtering. I added an additional drop drop menu which filters a gallery for "Mission Type". Combined with the Crew filter and Year filter, I'm able to correctly use the Sum and CountRows formulas. 

 

It seems that  in this scenario, it is a good practice to filter a gallery first, then pull data from it.  Rather than querying the database directly. Thanks @BitLord69 for your help. 

 

Brendon

BitLord69 Power Participant
Power Participant

Re: Sum and Filter with If Function

@BrendonBrooksP1 You're welcome! Not that I feel I actually said omething that helped you out, but it's always good to get another take on problems 🙂

 

Have a great day!

Jan-Erik

BrendonBrooksP1 Post Patron
Post Patron

Re: Sum and Filter with If Function

I did figure another work around...I think.

 

Since a single formula won't cut it, I decided to seperate filters and add them accordlingly.  The formula filters the table by the column's "Hoist Type", and then returns the adjacent amount. Those values are then added together.  Here's the initial formula:

 

 Sum(Filter(ytdgalminRs2.AllItems, Cm3HoistType1 = "Insert"), Cm3HoistType1Amt) 

The gallery is filtered by a drop down menu that filters the gallery by flight number prefix (2017).   Here's the rest of the formula:

 

Sum(Filter(ytdgalminRs2.AllItems, Cm3HoistType1 = "Insert"), Cm3HoistType1Amt) + Sum(Filter(ytdgalminRs2.AllItems, Cm3HoistType2 = "Insert"), Cm3HoistType1Amt) + Sum(Filter(ytdgalminRs2.AllItems, Cm3HoistType3 = "Insert"), Cm3HoistType3Amt) + Sum(Filter(ytdgalminRs2.AllItems, Cm3HoistType4 = "Insert"), Cm3HoistType4Amt) + Sum(Filter(ytdgalminRs2.AllItems, Cm3HoistType5 = "Insert"), Cm3HoistType5Amt) + Sum(Filter(ytdgalminRs2.AllItems, Cm3HoistType6 = "Insert"), Cm3HoistType6Amt)  

Now, i have to check to see if it's actually calculating the data correctly. But it looks like it is...

 

 

Brendon

 

 

 

 

 

Helpful resources

Announcements
MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

firstImage

New Ranks and Rank Icons released on April 21!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power Apps Community!

Top Solution Authors
Top Kudoed Authors
Users online (6,838)