Forum Discussion
Group rows with same and different values.
Hi, I am trying to group/summarize rows with the same values. The problem is some of rows are a match for 2/3 of the columns which screws up my data.
Example:
Before query is executed
| Dept Number | Cost | |
| [email protected] | 5656 | 2.00 |
| [email protected] | 1212 | 6.00 |
| [email protected] | 5656 | 3.00 |
| [email protected] | 5656 | 2.00 |
After query is executed
| Dept Number | Cost | |
| [email protected] | 5656 | 4.00 |
| [email protected] | 1212 | 6.00 |
| [email protected] | 5656 | 3.00 |
I need the similar rows with different cost to be rolled up as well. I am using a simple summarize query to achieve this but how can I get the rows with different cost to be grouped in as well?
hi Anonymous
try to create a calculated table like:
SumTable = ADDCOLUMNS( SUMMARIZE( TableName, TableName[Email], TableName[Dept Number] ), "Cost", CALCULATE(SUM(TableName[Cost])) )Also, in Power Query Editor, you can try to Groupby the query,
https://learn.microsoft.com/en-us/power-query/group-by
5 Replies
hi Anonymous
try to create a calculated table like:
SumTable = ADDCOLUMNS( SUMMARIZE( TableName, TableName[Email], TableName[Dept Number] ), "Cost", CALCULATE(SUM(TableName[Cost])) )Also, in Power Query Editor, you can try to Groupby the query,
https://learn.microsoft.com/en-us/power-query/group-by
That worked ! One more thing though im trying to add a date filter but Im getting an error that "," before cost is incorrect. Any Idea why?
DEFINE VAR MAveragePrice =
ADDCOLUMNS (
CALCULATETABLE (
SUMMARIZE ( 'Usage details amortized',
'Usage details amortized'[Tags - copy.DepartmentNumber],
'Usage details amortized'[Tags - copy.OwnerEmail]),
Usage details amortized'[Date])>= DATE ( 2022, 12, 01 ) &&
'Usage details amortized'[Date] <= DATE ( 2022, 12, 31 )
),
"Cost",
CALCULATE(SUM('Usage details amortized'[Cost])),
)
EVALUATE
MAveragePricehi Anonymous
CALCULATETABLE seems unnecessary, or?
p.s. please consider @someone, to continue a discussion.