Forum Discussion

tonyclifton's avatar
tonyclifton
Helper III
6 years ago
Solved

Add aggregated rows in Query Editor?

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.

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    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.

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    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.

  • Mariusz's avatar
    Mariusz
    Community Champion
    6 years ago

    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

     

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    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's avatar
      tonyclifton
      Helper III
      6 years ago

      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.