Search code examples
powerbidax

Summarise by multiple columns dax

I need to group the amounts in the table. There could be projects with different level 2 and 3 deliverers. I have tried this dax, but get: The expression specified in the query is not a valid table expression. Can you help?

Summarise = 
        var tab1=
        SUMMARIZE('Table',
        'Table'[Proj Ref],
        'Table'[Deliverer],
        'Table'[Level 2 Deliverer],
        'Table'[Level 3 Deliverer],
        'Table'[Period],
        "Totals",sum('Table'[Amount])
        )
        var a=sumx(tab1,[Totals])
RETURN a
Solution

  • You're using SUMMARIZE and then you are trying to use SUMX over a single variable that's your issue, just remove the variable.

    SummarisedAmount = 
    SUMMARIZE(
        'Table',
        'Table'[Proj Ref],
        'Table'[Deliverer],
        'Table'[Level 2 Deliverer],
        'Table'[Level 3 Deliverer],
        'Table'[Period],
        "Totals", SUM('Table'[Amount])
    )