Filter gallery using groupby function

[ScottyG]

I have Sharepoint list called “Revenue Data” similar to this revenue table and I would like to filter my gallery by year and quarter.  When I originally created the app, I only had data for 2021 and I filtered the gallery by quarter and month with no problem using the groupby function.  However, now that we are starting to add data for 2022, I want to filter by year and quarter, but cannot seem to get it to work right.  What am I doing wrong? 

ClientName	Year	Month	Quarter	Revenue	Expenses
A	2021	January	1	$  20,000.00	$       12,000.00
A	2021	February	1	$  16,000.00	$         7,000.00
A	2021	March	1	$   17,500.00	$         9,200.00
B	2021	January	1	$  22,000.00	$       13,000.00
B	2021	February	1	$  25,000.00	$       11,000.00
B	2021	March	1	$  31,000.00	$         9,500.00
A	2021	April	2	$  22,000.00	$       14,000.00
A	2021	May	2	$  19,000.00	$         7,000.00
A	2021	June	2	$  24,000.00	$         8,200.00
B	2021	April	2	$  34,000.00	$         9,800.00
B	2021	May	2	$  24,000.00	$         6,800.00
B	2021	June	2	$  26,000.00	$         7,400.00
A	2021	July	3	$  23,000.00	$         9,500.00
A	2021	August	3	$  29,000.00	$       16,000.00
A	2021	September	3	$  18,000.00	$       12,000.00
B	2021	July	3	$  24,000.00	$       11,000.00
B	2021	August	3	$  21,000.00	$         8,700.00
B	2021	September	3	$  29,000.00	$       11,600.00
A	2021	October	4	$  20,500.00	$       12,000.00
A	2021	November	4	$  27,000.00	$       13,000.00
A	2021	December	4	$  28,500.00	$       11,000.00
B	2021	October	4	$  35,500.00	$         9,500.00
B	2021	November	4	$  15,000.00	$         4,900.00
B	2021	December	4	$  26,000.00	$         8,600.00
A	2022	January	1	$  21,000.00	$         7,000.00
A	2022	February	1	$  18,000.00	$         8,500.00
B	2022	January	1	$  23,000.00	$         7,600.00
B	2022	February	1	$  24,000.00	$         9,000.00

Dropdown1 = Year

Dropdown2 = Quarter

Here is my gallery items formula:

AddColumns(GroupBy(Filter(‘RevenueData’, Quarter =Dropdown2.Selected.Result), "ClientName", "Quarter","DATA"),"REV",Sum(DATA, ‘Revenue’),"EXP",Sum(DATA,’Expenses'))

[Ramole]

Hi @ScottyG
Reza Dorrani just done that on YouTube how to group year watch 
https://www.youtube.com/watch?v=57ADxeo_13k 
@RezaDorrani 

[RandyHayes]

@ScottyG 

You just need to add the Year to your GroupBy.

AddColumns(
   GroupBy(
       Filter(RevenueData, 
           Quarter =Dropdown2.Selected.Result
       ), 
       "ClientName", 
       "Year",
       "Quarter",
       "DATA"
   ),
   "REV",Sum(DATA, Revenue),
   "EXP",Sum(DATA, Expenses)
)

I hope this is helpful for you.

[ScottyG]

Thanks for the reply.

I have added the Year to my groupby solution but I am still not getting the right result.  I am filtering on both Year and Quarter and my gallery pulls the correct year but it is giving me the total for the quarter of both years.  For example, the total for Q1 2021 is $131,500 and the total for Q1 2022 is $86,000.  However, when I filter on Q1, I get the result of $217,500.  Not sure if the issue is with my dropdown formulas or my gallery label items.

[ScottyG]

Thanks, I will watch the video again.  My main challenge is having the gallery to accurately sum only the quarter results of the selected year.  Currently, I am getting the sum of Q1 for both 2021 and 2022.

[Ramole]

@ScottyG 
Yes, watch all along the video and you will get it, you can also modify the formula showing by @RezaDorrani I have also tag him remember he's teaching large set of data of group by https://www.youtube.com/watch?v=57ADxeo_13k  

[ScottyG]

Yes, I have a very large data set and the 2nd part of the video is more in line with my use case.