Counting/Sum in a grouped table

[Anonymous]

I have the following for producing gallery items. I have it displaying as a gallery inside a gallery.

GroupBy(AddColumns(GroupBy(
            AddColumns(
                Projects,
                "Service",
                LookUp(
                    Programs,
                    ID = ProgID,
                    ServiceType.Value
                )
            ),"Service", "Status","tmp"),"Statcnt",CountRows(Filter(tmp,Status))),"Service","tmp2")

it produces the following:

tmp2 has records for each status with a count of rows of how many rows have that status.

This is the result before the last GroupBy:

I am displaying it like this:

If I want to add a total count by Service - something like "Beta - 35" how can I get the sum of the Statcnt for each status (for on the line of the outer gallery)? I tried Sum(ThisItem.tmp2.Statcnt) but I get an error. I'm sure I'm referencing it wrong , but everything I try gives me errors. Or should I add yet another column after the last GroupBy - I would think I shouldn't have to though.

[CarlosFigueira · Accepted solution]

You're almost there. The expression should be

Sum(ThisItem.tmp2, Statcnt)

The Sum function can take as arguments a table, and an expression to evaluate for each record of that table - in your case, the 'Statcnt' value for each record of 'tmp2'.

Hope this helps!

[Anonymous]

thanks! I knew it was something little I was missing!  Is there a way to also sum all in the outer gallery? So all counts added up. I had on a different gallery to sum based on the Gallery.AllItems a different count (and info), but with this one having the inner table that doesn't seem to work the same - yeah I'm pushing it. 🙂

[CarlosFigueira · Accepted solution]

Do you use that expression directly in the Gallery's Items property, or is it in a collection (and the collection is used in the gallery's Items property)? If it is in a collection, you can use the sum based on the collection itself instead of the gallery (and you can even add the count per service to the collection itself):

ClearCollect(
  myCollection,
  AddColumns(
    GroupBy(
      AddColumns(
        GroupBy(
          AddColumns(
            Projects,
            "Service",
            LookUp(
              Programs,
              ID = ProgID,
              ServiceType.Value
            )
          ),
          "Service",
          "Status",
          "tmp"
        ),
        "Statcnt", CountRows(Filter(tmp,Status))
      ),
      "Service",
      "tmp2"
    ),
    "ServiceCnt", Sum(tmp2, Statcnt)
  )
)

And outside the gallery you can use something like

Sum(myCollection, ServiceCnt)

If you use the expression directly in the gallery, an option is to use named formulas - which allows you to reference it in multiple places. So if you have the following formula:

MyNamedFormula =
  AddColumns(
    GroupBy(
      AddColumns(
        GroupBy(
          AddColumns(
            Projects,
            "Service",
            LookUp(
              Programs,
              ID = ProgID,
              ServiceType.Value
            )
          ),
          "Service",
          "Status",
          "tmp"
        ),
        "Statcnt", CountRows(Filter(tmp,Status))
      ),
      "Service",
      "tmp2"
    ),
    "ServiceCnt", Sum(tmp2, Statcnt)
  );

You can use the following expressions for the gallery and the outside label:

Gallery.Items: MyNamedFormula
Label.Text: Sum(MyNamedFormula, ServiceCnt)

Hope this helps!

[Anonymous]

I do have it currently as the Items of the gallery. thanks for the suggestion!