Add aggregated rows in Query Editor?

[tonyclifton]

Hello community,

is it possible to add aggregated rows for all existing columns in a table within the query editor?

In below image I want to aggregate based on YearMonth and Company so that a new "company" ("A+B") is created that holds the sum of all existing companies for all columns.

The reason I need to do this is because I have a budget table with the company name "A+B" and its values.

In order to compare actual and budget I need this company in my actual table.

If there are other options with DAX or so please feel free to share.

Thank you.

[v-lid-msft]

Hi @tonyclifton ,

We can create a calculated table to meet your requirement:

Actual =
UNION (
    'Table',
    SELECTCOLUMNS (
        ADDCOLUMNS (
            CROSSJOIN (
                DISTINCT ( 'Table'[YearMonth] ),
                FILTER (
                    CROSSJOIN (
                        SELECTCOLUMNS ( DISTINCT ( 'Table'[Company] ), "C1", [Company] ),
                        SELECTCOLUMNS ( DISTINCT ( 'Table'[Company] ), "C2", [Company] )
                    ),
                    [C1] < [C2]
                )
            ),
            "Value1",
            VAR co1 = [C1]
            VAR co2 = [C2]
            VAR ym = [YearMonth]
            RETURN
                CALCULATE (
                    SUM ( 'Table'[Value1] ),
                    'Table'[Company] IN { co1, co2 },
                    'Table'[YearMonth] = ym
                ),
            "Value2",
            VAR co1 = [C1]
            VAR co2 = [C2]
            VAR ym = [YearMonth]
            RETURN
                CALCULATE (
                    SUM ( 'Table'[Value2] ),
                    'Table'[Company] IN { co1, co2 },
                    'Table'[YearMonth] = ym
                ),
            "Value3",
            VAR co1 = [C1]
            VAR co2 = [C2]
            VAR ym = [YearMonth]
            RETURN
                CALCULATE (
                    SUM ( 'Table'[Value3] ),
                    'Table'[Company] IN { co1, co2 },
                    'Table'[YearMonth] = ym
                )
        ),
        "YearMonth", [YearMonth],
        "Company", [C1] & "+" & [C2],
        "Value1", [Value1],
        "Value2", [Value2],
        "Value3", [Value3]
    )
)

By the way, PBIX file as attached.

Best regards,

[tonyclifton]

Thanks for all the suggestions. I went one step "back" and unpivoted the table in order to create (reference) a new table like @Mariusz suggested so that I could create a sum column since there are many Value columns that I don't want to type in a query.
I think this works fine now.

[Mariusz]

Hi @tonyclifton 

Sure if you want to use your approach, please provide a sample table that can be copied.

You can always look at the DAX solution for Budget patterns below
https://www.daxpatterns.com/budget-patterns/  

But I imagine that you would need an extra column that creates a hierarchy, like

column	extra column
A	AB
B	AB
C	C

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Greg_Deckler · Accepted solution]

So, you could create a second query, reference your first query. Filter to just A and B. Create a column = "A+B". Now do a Group By to group by Date and your new column, appropriate aggregations.  Now add an Append step and Append your original query.

DAX would be similar using SUMMARIZE/GROUPBY and UNION.