cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
zmansuri
Super User
Super User

Need help with this complicated GroupBy and Filters.

q.jpg

 

 

 

q1.jpg

4 lists

Budget Tracker- which has projected expense

Projected Income - Which has projected Income

Actual Expense

Actual Income

 

all 4 list have 4 common field. Company, Year, Month, Office Location. I also need to provide filters on all 4 of this.

 

In the gallery i reference budget tracker list and add matching values from other 3 list using AddColumns. Problem is even though there is a data in Siyana (CompanyName)-August and September it doesn't show income data. October is showing correct data. Item code is little long, I hope you guys can help me. Pasting item code here. Can you checkout what i am doing wrong. Because i can't seem to find a problem. If it was for all the data I'd understand. But, some record shows correct data and some don't.

 

 

AddColumns(
    AddColumns(
        AddColumns(
            GroupBy(
                AddColumns(
                    Filter(
                        BudgetTracker,
                        Year.Value = drpYear_5.Selected.Value,
                        Company.Value = drpComp_7.Selected.Value || drpComp_7.Selected.Value = "All",
                        Month.Value in ComboBox1_1.SelectedItems || ComboBox1_1.Selected.Value = "All",
                        OfficeLocation.Value = drpOffice_4.Selected.Value || drpOffice_4.Selected.Value = "All"
                    ),
                    "CompanyTxt",
                    Company.Value,
                    "YearTxt",
                    Year.Value,
                    "MonthTxt",
                    Month.Value
                ),
                "CompanyTxt",
                "YearTxt",
                "MonthTxt",
                "DATA"
            ),
            "PlannedBudget",
            Sum(
                DATA,
                PlannedExpense
            ),
            "ActualExpense",
            Sum(
                Filter(
                    Expenses,
                    Company.Value = CompanyTxt,
                    ExpenseYear = YearTxt,
                    ExpenseMonth in MonthTxt,
                    OfficeLocation.Value in Concat(
                        DATA,
                        OfficeLocation.Value
                    )
                ),
                Expense
            ),
            "PlannedIncome",
            Sum(
                Filter(
                    ProjectedIncome,
                    Company.Value = CompanyTxt,
                    Year.Value = YearTxt,
                    Month.Value in MonthTxt,
                    OfficeLocation.Value in Concat(
                        DATA,
                        OfficeLocation.Value
                    )
                ),
                ProjectedIncome
            ),
            "OpeningBalance",
            LookUp(
                OpeningBalance,
                Company.Value = CompanyTxt && IncomeYear = YearTxt && IncomeMonth in MonthTxt,
                Balance
            ),
            "ActualIncome",
            Sum(
                Filter(
                    ActualIncome,
                    Company.Value = CompanyTxt,
                    IncomeYear = YearTxt,
                    IncomeMonth in MonthTxt,
                    OfficeLocation.Value in Concat(
                        DATA,
                        OfficeLocation.Value
                    )
                ),
                ActualIncome
            )
        ),
        "RemainingBudget",
        PlannedBudget - ActualExpense,
        "DifferenceInIncome",
        ActualIncome - PlannedIncome,
        "ActualProfit",
        ActualIncome - ActualExpense,
        "PlannedProfit",
        PlannedIncome - PlannedBudget
    ),
    "ProfitDifference",
    ActualProfit - PlannedProfit,
    "ClosingBalance",
    OpeningBalance + ActualProfit
)
1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User
Super User

@zmansuri 

Yes, your use of the And operator (&&) is very restrictive.  I believe you will want to make that an Or operator instead (||)...but, given what I am reading in your Formula, that is perhaps not the real logic you want.

One thing I do see in regard to the logic is that in your added column filter criteria, you are checking to see if the ExpenseMonth is in MonthTxt, but in your inner AddColumns, you are assigning a single value to MonthTxt...not multiple.  So it would seem you need the months in your "DATA" column table and check to see if the Month.Value is in that table...just as you do for the location.

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

11 REPLIES 11
zmansuri
Super User
Super User

@WarrenBelz @RandyHayes  tagging you guys because you are good with many lines of code. Thanks

RandyHayes
Super User
Super User

@zmansuri 

I reviewed your Formula and there is nothing syntactically wrong that I can spot.

So, I would start by looking at the inner filter - are you able to see the results you expect in the formula editor for that filter?

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

q2.jpg

Thank you for the prompt reply.

No, It's not getting data there too. Only difference between Actual and Projected lists is. In the Projected i have choice columns for month and year. Because they will be entered at the start of the year. And in the actual list user will daily enter data. So there is a date field. And in the sharepoint i have calculated column named "IncomeMonth" and "ExpenseMonth" respectively that takes month from the date column.

 

Are you sure about the code? Because i am guessing something is wrong in the month filters. Reason is i have another dashboard where i don't groupby using month column.  we see aggregate data for the whole year. Figures are correct. posting that dashboard pic and item property. Thanks

q3.jpg

 

 

Filter(
    AddColumns(
        AddColumns(
            AddColumns(
                GroupBy(
                    AddColumns(
                        Filter(
                            BudgetTracker,
                            Year.Value = drpYear_4.Selected.Value,
                            Company.Value = drpComp_6.Selected.Value || drpComp_6.Selected.Value = "All",
                            OfficeLocation.Value = drpOffice_3.Selected.Value || drpOffice_3.Selected.Value = "All",
                            Month.Value in ComboBox1.SelectedItems || ComboBox1.Selected.Value = "All"
                        ),
                        "CompanyTxt",
                        Company.Value,
                        "YearTxt",
                        Year.Value
                    ),
                    "CompanyTxt",
                    "YearTxt",
                    "DATA"
                ),
                "PlannedBudget",
                Sum(
                    DATA,
                    PlannedExpense
                ),
                "ActualExpense",
                Sum(
                    Filter(
                        Expenses,
                        CompanyTxt = Company.Value && YearTxt = ExpenseYear && ExpenseMonth in Concat(
                            DATA,
                            Month.Value
                        ) && OfficeLocation.Value in Concat(
                            DATA,
                            OfficeLocation.Value
                        )
                    ),
                    Expense
                ),
                "PlannedIncome",
                Sum(
                    Filter(
                        ProjectedIncome,
                        Company.Value = CompanyTxt && YearTxt = Year.Value && Month.Value in Concat(
                            DATA,
                            Month.Value
                        ) && OfficeLocation.Value in Concat(
                            DATA,
                            OfficeLocation.Value
                        )
                    ),
                    ProjectedIncome
                ),
                "OpeningBalance",
                LookUp(
                    OpeningBalance,
                    Company.Value = CompanyTxt && YearTxt = IncomeYear && IncomeMonth in Concat(
                        DATA,
                        Month.Value
                    ) && OfficeLocation.Value in Concat(
                        DATA,
                        OfficeLocation.Value
                    ),
                    Balance
                ),
                "ActualIncome",
                Sum(
                    Filter(
                        ActualIncome,
                        Company.Value = CompanyTxt && IncomeYear = YearTxt && IncomeMonth in Concat(
                            DATA,
                            Month.Value
                        ) && OfficeLocation.Value in Concat(
                            DATA,
                            OfficeLocation.Value
                        )
                    ),
                    ActualIncome
                )
            ),
            "RemainingBudget",
            PlannedBudget - ActualExpense,
            "DifferenceInIncome",
            ActualIncome - PlannedIncome,
            "ActualProfit",
            ActualIncome - ActualExpense,
            "PlannedProfit",
            PlannedIncome - PlannedBudget
        ),
        "ProfitDifference",
        ActualProfit - PlannedProfit,
        "ClosingBalance",
        OpeningBalance + ActualProfit
    ),
    CompanyTxt = drpComp_6.Selected.Value || drpComp_6.Selected.Value = "All",
    YearTxt = drpYear_4.Selected.Value
)

 

RandyHayes
Super User
Super User

@zmansuri 

Let's start by looking at the inner filter - are you able to see the results you expect in the formula editor for that filter?

Your image shows the results from the entire Formula.

In your Formula, place your cursor at the ending paren of the Inner Filter and see if the resulting table that the formula editor shows indicates the data that you expect from the inner filter itself...not the whole Formula result.

 

So, in your Formula, place your cursor here:

RandyHayes_0-1630075236842.png

If the formula editor does not provide a resulting table to look over, then highlight the entire Filter function from the word Filter to the closing paren.

 

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

q4.jpg

 

Ok. Sorry for the confusion. But, same thing is happening with year Dashboard too. When i filter that by Office Location. Aggerate data is correct.

 

And  i get correct data when i check in internal filters as you said:

q6.jpg

Above picture has "Ahd_A" office selected. Year is 2021. Month is All, Company is Siyana

It has 150000 budget allocated.

RandyHayes
Super User
Super User

@zmansuri 

Okay, I'm getting an idea from that last message as to what is a "Good" result for expenses (I believe you are stating that the image shows what is expected from both the app screen as well as the inner filter).

If that is correct, then we're focused on the Income amounts missing...please validate that I am on the correct track.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Ok. I notice something else, maybe you can figure it out. I think problem lies with this kind of filters i have used in AddColumns:

 

ExpenseMonth in Concat(
                            DATA,
                            Month.Value
                        ) && OfficeLocation.Value in Concat(
                            DATA,
                            OfficeLocation.Value
                        )
                    

 Three office locations: Ahd_A, Ahd_B, Hyd_A. When i select hyd_A in the filter it shows accurate data because IncomeMonth of hyd_A is same. October.

q9.jpgq10.jpg

 

But, When i select Ahd_B which has 2 different income month's entry. It shows wrong data for Actual income. Result should be 2,10,000. But, it shows 1,00,000. And Projected income is not even there. So basically it is only fetching August's Data and not Septembers. I hope i am making sense.

 

q11.jpgq12.jpgq13.jpg

RandyHayes
Super User
Super User

@zmansuri 

Yes, your use of the And operator (&&) is very restrictive.  I believe you will want to make that an Or operator instead (||)...but, given what I am reading in your Formula, that is perhaps not the real logic you want.

One thing I do see in regard to the logic is that in your added column filter criteria, you are checking to see if the ExpenseMonth is in MonthTxt, but in your inner AddColumns, you are assigning a single value to MonthTxt...not multiple.  So it would seem you need the months in your "DATA" column table and check to see if the Month.Value is in that table...just as you do for the location.

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
zmansuri
Super User
Super User

@RandyHayes i finally figured it out by following record by record. 

My reference list is BudgetTracker. Which has budget like this

CompanyOfficeLocationYearMonthPlannedExpense
SiyanaAhd_A2021September1,50,000

When i use AddColumn this is my code for all of them. For eg. For ProjectedIncome:

 

"PlannedIncome",
                Sum(
                    Filter(
                        ProjectedIncome,
                        Company.Value = CompanyTxt && YearTxt = Year.Value && Month.Value in Concat(
                            DATA,
                            Month.Value
                        ) && OfficeLocation.Value in Concat(
                            DATA,
                            OfficeLocation.Value
                        )
                    ),
                    ProjectedIncome
                

Data Inside Projected Income

CompanyOfficeLocationYearMonthPlannedExpense
SiyanaAhd_A2021August1,25,000

 

So, when my addcolumn code runs. It matches with year, company and office location but it doesn't find month in the budget tracker list. hence no value there. and that's why when i select office location all. aggregate data is correct.

 

Thank you so much for your patience and your help. couldn't have done it without you.

 

Do you have any suggestions or tweaks for my code?

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on June 15, 2022 at 8am PDT.

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Top Kudoed Authors
Users online (2,474)