cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Microsoft
Microsoft

GroupBy with multiple columns and Sum one column in CDS Entity

I have a CDS Entity 'Cost Accounting Measurements' , I created one view "Report" with columns "environmentid", "resourcetype", "resource", "subledgeraccount", "costelement", "magnitude". I want to add a column to get the sum(magnitude) group by "environmentid", "resourcetype", "resource", "subledgeraccount", "costelement".

 

environmentid

resourcetype

resource

subledgeraccount

costelement

magnitude

22222222-2222-2222-2222-222222222222

Currency

USD

Inventory

Material

330.00

22222222-2222-2222-2222-222222222222

Currency

USD

Inventory

Material

99,892.72

22222222-2222-2222-2222-222222222222

Product

D0001

Transit

 

-6

22222222-2222-2222-2222-222222222222

Product

D0001

Inventory

 

-2

 I tried to add one  "Data table" control in the power apps with data source is current view, and use the below function to add the calculated column. But the question there was the warning for the expression, actually for the sum(source, column) function, I cannot find the the column "cas_magnitude".

 AddColumns(GroupBy('Cost Accounting Measurements', "cas_environmentid", "cas_resourcetype", "cas_resource","cas_subledgeraccount", "cas_costelement", "all_data"), "sum_magnitude", Sum(all_data, "cas_magnitude"))

 

Thanks in advance!!!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Hi @Ivy_Liu 

From your screenshot, it appears that you're setting the text property of a column in your data table.

For this to work, you should set the Items property of your data table to your AddColumns/Group By formula instead. This should hopefully take you further.

 

timl_0-1603790838049.png

 

View solution in original post

7 REPLIES 7
Highlighted
Super User III
Super User III

Hi @Ivy_Liu ,

You do not need cas_magnitude in quotes

AddColumns(
   GroupBy(
      'Cost Accounting Measurements', 
      "cas_environmentid", 
      "cas_resourcetype", 
      "cas_resource",
      "cas_subledgeraccount", 
      "cas_costelement", 
      "all_data"
   ), 
   "sum_magnitude", 
   Sum(
      all_data, 
      cas_magnitude
   )
)

 

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.

Highlighted

Thanks WarrenBelz for quick response. I tried to remove the quote, but it still has the error.

Please refer to the screenshot, 

Screenshot for sum functionScreenshot for sum function

Highlighted

Thanks @Ivy_Liu ,

What type of field is cas_magnitude and is that the correct (original) field name?

The syntax is correct if you were simply trying to total a numeric column.

 

Highlighted

Hi  , 

 

The field type is decimal number, does this support sum() function? 

Ivy_Liu_0-1603787677882.png

 

Highlighted

@Ivy_Liu 

I am not sure (I am a SharePoint user), but I noticed that actual field name is Magnitude.

Highlighted

Hi @Ivy_Liu 

From your screenshot, it appears that you're setting the text property of a column in your data table.

For this to work, you should set the Items property of your data table to your AddColumns/Group By formula instead. This should hopefully take you further.

 

timl_0-1603790838049.png

 

View solution in original post

Highlighted

Hi  

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (11,757)