Solved: Need help with this complicated GroupBy and Filter...

zmansuri · ‎08-27-2021

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
)

RandyHayes · ‎08-27-2021

@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

zmansuri · ‎08-27-2021

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

RandyHayes · ‎08-27-2021

@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!

zmansuri · ‎08-27-2021

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

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 · ‎08-27-2021

@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:

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!

zmansuri · ‎08-27-2021

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:

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

It has 150000 budget allocated.

RandyHayes · ‎08-27-2021

@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!

zmansuri · ‎08-27-2021

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.

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.

RandyHayes · ‎08-27-2021

@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 · ‎08-27-2021

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

My reference list is BudgetTracker. Which has budget like this

Company	OfficeLocation	Year	Month	PlannedExpense
Siyana	Ahd_A	2021	September	1,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

Company	OfficeLocation	Year	Month	PlannedExpense
Siyana	Ahd_A	2021	August	1,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?

Need help with this complicated GroupBy and Filters.

Helpful resources

Join us for Microsoft Power Platform Conference

Power Platform Community Call

Register for a Free Workshop

New Release Planning Portal (Preview)