cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MartijnA80
Frequent Visitor

Groupby Username and add other columns

Working on a scenario where I have external data 

 

Screenshot_20210921-193033_SharePoint.jpg

In a gallery the items need to be grouped by "Agent". As this is a user field, I need to add a column which I did : 

 

GroupBy(AddColumns(MySharepointList, "UserDisplayName", Agent.DisplayName), "UserDisplayName", "GroupByUsersDisplayName")

 

That works great, the gallery now only shows unique users. However no other columns are available after this. 

 

I would need to add the rest of the columns as well and based on 3 combobox controls (fiscal week, fiscal month, fiscal quarter) each unique user would have to show the sums of the Satisfied, Neutral and Dissatisfied fields.

 

I'm not even sure if this is doable in a gallery, and I have been tinkering with addcolumns logic but to no avail.

 

Any direction is more than welcome!

1 ACCEPTED SOLUTION

Accepted Solutions
WarrenBelz
Super User
Super User

Hi @MartijnA80 ,

This is the pattern for the totals

AddColumns(
   GroupBy(
      AddColumns(
         MySharepointList, 
         "UserDisplayName", 
         Agent.DisplayName
      ), 
      "UserDisplayName", 
      "GroupByUsersDisplayName"
   ),
   "WeekTotal",
   Sum(
      GroupByUsersDisplayName,
      'Fiscal Week'
   ),	  
   "MonthTotal",
   Sum(
      GroupByUsersDisplayName,
      'Fiscal Month'
   ),
   "QuarterTotal",
   Sum(
      GroupByUsersDisplayName,
      'Fiscal Quarter'
   )
)

 

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

2 REPLIES 2
WarrenBelz
Super User
Super User

Hi @MartijnA80 ,

This is the pattern for the totals

AddColumns(
   GroupBy(
      AddColumns(
         MySharepointList, 
         "UserDisplayName", 
         Agent.DisplayName
      ), 
      "UserDisplayName", 
      "GroupByUsersDisplayName"
   ),
   "WeekTotal",
   Sum(
      GroupByUsersDisplayName,
      'Fiscal Week'
   ),	  
   "MonthTotal",
   Sum(
      GroupByUsersDisplayName,
      'Fiscal Month'
   ),
   "QuarterTotal",
   Sum(
      GroupByUsersDisplayName,
      'Fiscal Quarter'
   )
)

 

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

Seems as if we are almost here...

I had to change the code a bit, but once that was done it worked perfect (see code below)

AddColumns(
        GroupBy(
                AddColumns(
                cx_agent_performance,
                "UserDisplayName",
                Agent.DisplayName
            ),
            "UserDisplayName",
            "GroupByUserDisplayName"
        ),
        "TotalSat",
        Sum(GroupByUserDisplayName,
            Satisfied
        ),
        "TotalDissat",
        Sum(GroupByUserDisplayName,
            Dissatisfied
        ),
        "TotalNeutral",
        Sum(GroupByUserDisplayName,
            Neutral
        ))

I still need to add the filter option in here somewhere, and no matter where i tried adding it, it did not work

The rest of the functionality works as you can see in the screen below (i will make this look a bit nicer later on)

 

So the final piece is how to get the data filtered based on the selection of the dropdown (week_select4)

 

Example.png

 

There are some other questions (such as: how to make all bars the same color (or ideally even format if below target then different color) and how to get a % value on the axis instead of the numeric one. I will search the threads and post a new topic as needed for that.

 

Thanks for the help so far, if anyane can point me into the final piece (the filter based on DD selection) it would be awesome!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

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