cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
johncao1986
Frequent Visitor

How to sum the values by month and by person

I want to sum the "OT Hours" by "Created by", by "OT Types", by "Status"

 

and if once the "Created by" , "Status" is Approved, Sum of OT hours >48 in the current Month, the submit button is invisible.

 

Can you guide how to set it?

 

Here attached the data source in Sharepoint List

johncao1986_0-1652431837652.png

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

@johncao1986 ,


This will give you the SUM value you want, replace the "VALUES" by your actual values from inputs.

 

Sum(
    Filter(
        'SharePoint List',
        'Created By' = "VALUE" And 'OT Types' = "TYPE.VALUE" And Status = STATUS.VALUE)
    ),
    'OT Types'
)

 

If you wish to hide your button if the sum of hours > 48  AND only from this month, use below formula on your submit button Visible property. This will work but will probably give delegation. Try changing the dates to variables and it might do the trick.

 

Sum(
    Filter(
        'SharePoint List',
        'Created By' = "VALUE" And 'OT Types' = "TYPE.VALUE" And Status = STATUS.VALUE And 'Created On' >= Date(
            Year(Today()),
            Month(Today()),
            1
        ) And 'Created On' <= Date(
            Year(Today()),
            Month(Today()) + 2,
            0
        )
    ),
    One
) > 48

 

Best Regards,

Anthony

View solution in original post

4 REPLIES 4
Anthony_Dob
Responsive Resident
Responsive Resident

Can you please provide a screenshot of what you are trying to achieve. Do you just want a Table which has 4 columns -> Created By, Status, OT Types and Sum of Hours?

RusselThomas
Microsoft
Microsoft

Hi @johncao1986 ,

 

How you want to display this will ultimately determine the method as there are a few ways you can do this.

 

That said, you're probably going to want to use a combination of Sum() and GroupBy() functions, so perhaps give those a read.  I agree with @Anthony_Dob - some more info would be required to give you a more concise answer, but perhaps some examples in the meantime;

 

Table grouped by a column:

 

ClearCollect(summaryTable, GroupBy(myDataSource, "Created By", "groupedRowsColumn"))

 

groupedRowsColumn then contains a table of the rows that are grouped by that value.

Created bygroupedRowsColumn

     Joe

OT FromOT EndDining TimesOT Hours
12/5/202212/5/2022323
13/5/202213/5/2022212
    Sue
OT FromOT EndDining TimesOT Hours
14/5/202216/5/2022023
13/5/202214/5/202214

You can adjust this formula to also add a column to sum the hours for the current grouping;

 

With({groupedTable: GroupBy(myCollection, "Created By", "groupedRowsColumn")},  
    Collect(summaryTable, AddColumns(groupedTable, "Total", Sum(ThisRecord.groupedRowsColumn, OT_Hours)))
)

 

summaryTable contents then looks like this

Created bygroupedRowsColumn     Total

     Joe

OT FromOT EndDining TimesOT Hours
12/5/202212/5/2022323
13/5/202213/5/2022212
     35
     Sue
OT FromOT EndDining TimesOT Hours
14/5/202216/5/2022023
13/5/202214/5/202214
     27

You can then use similar logic for your other groupings.

Hope this helps,

RT

Hi @Anthony_Dob @RusselThomas 

 

I use sharepoint list as data source, in my App, I want to show the Total OT Hours this month of the Requestor

 

Can you guide me how to set the formula? Thanks!

johncao1986_2-1652665559917.png

 

 

@johncao1986 ,


This will give you the SUM value you want, replace the "VALUES" by your actual values from inputs.

 

Sum(
    Filter(
        'SharePoint List',
        'Created By' = "VALUE" And 'OT Types' = "TYPE.VALUE" And Status = STATUS.VALUE)
    ),
    'OT Types'
)

 

If you wish to hide your button if the sum of hours > 48  AND only from this month, use below formula on your submit button Visible property. This will work but will probably give delegation. Try changing the dates to variables and it might do the trick.

 

Sum(
    Filter(
        'SharePoint List',
        'Created By' = "VALUE" And 'OT Types' = "TYPE.VALUE" And Status = STATUS.VALUE And 'Created On' >= Date(
            Year(Today()),
            Month(Today()),
            1
        ) And 'Created On' <= Date(
            Year(Today()),
            Month(Today()) + 2,
            0
        )
    ),
    One
) > 48

 

Best Regards,

Anthony

Helpful resources

Announcements
Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Top Kudoed Authors
Users online (1,606)