topic Re: Group rows with same and different values. in DAX Commands and Tips

Group rows with same and different values.

Anonymous — Wed, 25 Jan 2023 22:45:42 GMT

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

Email	Dept Number	Cost
name@mail.com	5656	2.00
name2@mail.com	1212	6.00
name@mail.com	5656	3.00
name@mail.com	5656	2.00

After query is executed

Email	Dept Number	Cost
name@mail.com	5656	4.00
name2@mail.com	1212	6.00
name@mail.com	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?

Re: Group rows with same and different values.

FreemanZ — Wed, 25 Jan 2023 22:51:59 GMT

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

Re: Group rows with same and different values.

Anonymous — Thu, 26 Jan 2023 01:09:31 GMT

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
MAveragePrice

Re: Group rows with same and different values.

FreemanZ — Thu, 26 Jan 2023 01:24:38 GMT

hi Anonymous

CALCULATETABLE seems unnecessary, or?

p.s. please consider @someone, to continue a discussion.

Re: Group rows with same and different values.

Anonymous — Thu, 26 Jan 2023 01:27:41 GMT

FreemanZ Maybe. I've tried Omitting it but I'm sure I got the formatting wrong. Where do you suppose I put the date filter?

Re: Group rows with same and different values.

FreemanZ — Thu, 26 Jan 2023 13:06:48 GMT

hi Anonymous

sorry, i mislook part of your previous code. In cases like that, the filtering shall apply outside of ADDCOLUMNS.

try like:

DEFINE VAR MAveragePrice = CALCULATETABLE ( ADDCOLUMNS ( SUMMARIZE ( 'Usage details amortized', 'Usage details amortized'[Tags - copy.DepartmentNumber], 'Usage details amortized'[Tags - copy.OwnerEmail]), 'Usage details amortized'[Date] ), "Cost", CALCULATE(SUM('Usage details amortized'[Cost])) ), 'Usage details amortized'[Date])>= DATE ( 2022, 12, 01 ) && 'Usage details amortized'[Date] <= DATE ( 2022, 12, 31 ) ) EVALUATE MAveragePrice