cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ntt56k4
Helper IV
Helper IV

Avoid duplicates

I have the following formula on the label below to concat and sum the items in my gallery below but I cannot figure out a way to avoid duplicate values of Account # 1234. I am looking for a way to total all account #1234 and 1235 and display them separately like 1234 = 609.35 / 1235 = 1300.44. Any help would be appreciated. 

 

Label with Text = Concat(Gallery.AllItems,Account & " = " & Text(Sum(Gallery.AllItems,Total),"[$-en-US]$###,##.00") & " / ") 

 

 

 

Gallery 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
WarrenBelz
Super User III
Super User III

Hi @ntt56k4 ,

Note free-typed (so watch commas and brackets), but to produce the result you have displayed, the structure would be something like this. The With() statement is only to get rid of the last "/".

With(
   {
      wTotals:
      Concat(
         AddColumns(
            GroupBy(
               GalleryName.AllItems,
               "Account",
               "Data"
            ),
            "AllTotal",
            Account & " = " & 
            Text(
               Sum(
                  Data,
                  Total
               ),
               "[$-en-US]$###,##.00"
            )
         ),
         AllTotal & " / "
      )
   },
   Left(
      wTotals,
      Len(wTotals)-3
   )
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

View solution in original post

7 REPLIES 7
WarrenBelz
Super User III
Super User III

Hi @ntt56k4 ,

Note free-typed (so watch commas and brackets), but to produce the result you have displayed, the structure would be something like this. The With() statement is only to get rid of the last "/".

With(
   {
      wTotals:
      Concat(
         AddColumns(
            GroupBy(
               GalleryName.AllItems,
               "Account",
               "Data"
            ),
            "AllTotal",
            Account & " = " & 
            Text(
               Sum(
                  Data,
                  Total
               ),
               "[$-en-US]$###,##.00"
            )
         ),
         AllTotal & " / "
      )
   },
   Left(
      wTotals,
      Len(wTotals)-3
   )
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

View solution in original post

thanks for your response but I am getting the error message below. It looks like I am not entering the correct formula for the GropuBy. Are you referring to my SP data on the GroupBy? or one of the SharePoint column names? 

 

I meant AddtoColumn

ntt56k4_0-1627234774526.png

 

@ntt56k4 ,

As I said, check commas and brackets (I do not have the "red line" check in Notepad you have in Power Apps).

A comma missing after "AllTotal" (now fixed) I have also added the description which I omitted before.

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

awsome very much appreciated for your help

Sorry to bother you again but how do I also integrate the column CostCenter (shows TC30 on the gallery) to the formula, so it also sum the TC30? I tried messing around but couldn't come up with the correct formula.  

ntt56k4
Helper IV
Helper IV

nevermind I just figured it out just needed to add the & sign and repeat the formula

 

With({wTotals:Concat(AddColumns(GroupBy(Gallery.AllItems,"Account","Data"),"AllTotal",Account&" = "&Text(Sum(Data,Total),"[$-en-US]$###,##.00")),AllTotal&" / ")},Left(wTotals,Len(wTotals)-2)) & " / " & With({wTotals:Concat(AddColumns(GroupBy(Gallery.AllItems,"CostCenter","Data"),"AllTotal",CostCenter &" = "&Text(Sum(Data,Total),"[$-en-US]$###,##.00")),AllTotal&" / ")},Left(wTotals,Len(wTotals)-2))

@ntt56k4 ,

Yes - a bit messy, but if you want to group by two fields separately, you have to repeat it all.

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (1,723)