SQL-like GROUP BY in a LIST() or SELECT()?

[Jon_S]

Hi Community!

I was wondering if there’s a way to have a SQL-like GROUP BY statement inside a SELECT()?

Has anyone bumped into this challenge before? Maybe there’s clever workaround?

[Jon_S]

Here’s a screenshot that might help

[Aleksi]

You could use SUM(SELECT(TableName[QTY],[Vehicle Name]=[_THISROW].[Vehicle Name])). It will calculate all same vehicles in every row.

[Jason808]

I'm interested in the same thing. I want to be able to show a list as a result of a group by operation. I am able to do a sum for a group using sum/select but want to pull the name and all the sums for the distinct values in the table.

[Jason808]

FYI the closest I was able to get was to use the "Group by" and "Group aggregate" settings in the View Options. Unfortunately there isn't a way to suppress the actual detail records from showing so it looks like I may need to use a table structured with the records I want to see that I can calculate the subtotals for.

If anyone can think of a better solution, here is my setup and my desired result:

Table InventoryLog, Fields: Item, Location, Amount
I would like to get a list on the Item table that shows one record per location, showing location, sum of amount for that location

Thanks!