Group data by

(0) Share

Report

Posted on by Fada

152

Hello,

I have a list as data source in Sharepoint online named Data_list with theses columns: ID, Start_date, End_date, Username(type person), perdiem(number).

In power apps, I would like to group my data in a gallery by usernane and sum (perdiem) so that I can have a report like a pivot table with Excel. I would also like to filter the data by Start_date and End_Date.

I made a simulation in Excel. Attached the table and the report that I would like.

I struggle to do that despite several tutorials on you tube.

Thanks.

Categories:

Building Power Apps

Report.JPG

data_list.JPG

I have the same question (0)

All responses (9)

Answers (2)

Verified answer

NandiniBhagya20 4,667 Super User 2024 Season 1 on at

Like (0)

Report
Hi @Fahd ,
Add a gallery control and put below formula in items property of Gallery -

GroupBy(AddColumns(Data_List,"UserVal",'Created By'.DisplayName),"UserVal","GroupData")

Add two labels in the gallery. Put below formula in Text property of Label1 to display user name -

ThisItem.UserVal

And use below formula in Text property of Label2 to display Count -

Sum(ThisItem.GroupData,perdiem)

Was this reply helpful? Yes No
Fada 152 on at

Like (0)

Report

Hi @NandiniBhagya20 , thanks for your response. I have tested your code and it works well. But, I forgot to mention that I have a choice column named Status. So I only want to have Sum(ThisItem.GroupData,perdiem) where status is Approved. Please, can you help? Attached a screenshot of the Sharepoint and power apps.
Thanks

data.JPG

sum.JPG

Was this reply helpful? Yes No
Fada 152 on at

Like (0)

Report

Hi @NandiniBhagya20 , I found out the solution. Below the code:
GroupBy(Filter(AddColumns(Missions,"Userval", Conducteur.DisplayName),Statut.Value="Approuved"), "Userval","GroupData")
Thank you very much for your help

Was this reply helpful? Yes No
NandiniBhagya20 4,667 Super User 2024 Season 1 on at

Like (0)

Report
Hi @Fahd ,
You can use Filter function for filtering Status column with Approved value. Try using below formulas (anyone) -
1. The below formula will show sum of perdiem column where status is Approved

Sum(Filter(ThisItem.GroupData, Status.Value = "Approved"),perdiem)

2. The below formula will filter gallery with Status = Approved

GroupBy(AddColumns(Filter(Data_List, Status.Value="Approved"),"UserVal",'Created By'.DisplayName),"UserVal","GroupData")

Was this reply helpful? Yes No
Fada 152 on at

Like (0)

Report

Hi @NandiniBhagya20 , your code work perfectly, but is there a way to sort the sum by descending?
Thanks

data1.JPG

Was this reply helpful? Yes No
Verified answer

NandiniBhagya20 4,667 Super User 2024 Season 1 on at

Like (1)

Report
Hi @Fahd ,
Use below function in Items property of Gallery -

Sort(AddColumns(GroupBy(AddColumns(Filter(Missions,Statut.Value="Approved"),"Userval",Conducteur.DisplayName),"Userval","GroupData"),"sumN",Sum(GroupData,perdiem)),sumN,SortOrder.Descending)

And you can use ThisItem.sumN to display sum of perdiem column.

Was this reply helpful? Yes No
Fada 152 on at

Like (0)

Report

Hi @NandiniBhagya20 , thank you for your response. When I test your code it shows an errors (Expected Text value). Attached a screenshot. Thanks

error.JPG

Was this reply helpful? Yes No
NandiniBhagya20 4,667 Super User 2024 Season 1 on at

Like (1)

Report

Hi @Fahd,
Where have you put the formula? Try adding it to Items property of Gallery.

Was this reply helpful? Yes No
Fada 152 on at

Like (0)

Report

Hi @NandiniBhagya20 , thank you very much, it is perfect, I put it in the wrong place.👍

Was this reply helpful? Yes No