Forum Discussion
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 "...
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.
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,
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.