Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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   E...
DAX
power bi
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    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

     

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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

FreemanZ's avatar
FreemanZ
Super User
3 years ago

hi Anonymous 

CALCULATETABLE seems unnecessary, or?

 

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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?

    • FreemanZ's avatar
      FreemanZ
      Super User
      3 years ago

      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