Grouping by and separating by year

[Anonymous]

Hello,

I have the following table:

Order No	Order Year	City	State	Customer Type	Order Priority	Product Category	Profit Margin	Order Quantity
5014-1	2021	Melbourne	VIC	Home Office	High	Office Supplies	2.06	29
5016-1	2020	Melbourne	VIC	Home Office	High	Office Supplies	1.87	29
5018-1	2021	Sydney	NSW	Corporate	Critical	Office Supplies	1.3	42
5019-1	2021	Melbourne	VIC	Home Office	High	Technology	45.97	6
5020-1	2020	Sydney	NSW	Consumer	Critical	Office Supplies	1.2	17
5023-1	2021	Melbourne	VIC	Home Office	Low	Office Supplies	1.51	47
5024-1	2021	Sydney	NSW	Corporate	Critical	Office Supplies	63.54	32
5025-1	2021	Sydney	NSW	Consumer	Critical	Office Supplies	0.59	33
5027-1	2021	Sydney	NSW	Small Business	Not Specified	Office Supplies	36.19	8
5029-1	2021	Sydney	NSW	Corporate	Not Specified	Office Supplies	3.27	48
5031-1	2021	Melbourne	VIC	Corporate	Medium	Office Supplies	2.75	18
5033-1	2021	Sydney	NSW	Corporate	Medium	Office Supplies	36.19	3
5034-1	2021	Sydney	NSW	Corporate	High	Office Supplies	22.08	50
5036-1	2021	Melbourne	VIC	Corporate	Critical	Office Supplies	0.43	50
5037-1	2021	Sydney	NSW	Home Office	Critical	Office Supplies	4.92	16
5038-1	2021	Melbourne	VIC	Corporate	Low	Office Supplies	0.43	38
5039-1	2020	Sydney	NSW	Corporate	Medium	Office Supplies	2.69	22
5040-1	2020	Sydney	NSW	Small Business	Low	Office Supplies	1.58	48
5043-1	2020	Sydney	NSW	Small Business	High	Office Supplies	1.16	22

I need to group by city, state and customer type, calculating the aggregated sum of order quantity and profit margin. But also i need to differentiate by year, so my guess is that the fourth grouping has to be year. What I did was the following:

• I created a table with this DAX formula:

Agg =
GROUPBY (
Data,
Data[City],
Data[State],
Data[Customer Type],
Data[Order Year],
"Revenue agg", SUMX ( CURRENTGROUP (), Data[Profit Margin] ),
"Quantity agg", SUMX ( CURRENTGROUP (), Data[Order Quantity] )
)

• The formula creates this table:

Data_City	Data_State	Data_Customer Type	Data_Order Year	Revenue agg	Quantity agg
Melbourne	VIC	Home Office	2021	$49.54	82
Melbourne	VIC	Home Office	2020	$1.87	29
Sydney	NSW	Corporate	2021	$126.38	175
Sydney	NSW	Consumer	2020	$1.2	17
Sydney	NSW	Consumer	2021	$0.59	33
Sydney	NSW	Small Business	2021	$36.19	8
Melbourne	VIC	Corporate	2021	$3.61	106
Sydney	NSW	Home Office	2021	$4.92	16
Sydney	NSW	Corporate	2020	$2.69	22
Sydney	NSW	Small Business	2020	$2.74	70

The problem that I have is that I don't know how to get from the previous table, to the following desired output:

Data_City	Data_State	Data_Customer Type	Revenue agg 2020	Quantity agg 2020	Revenue agg 2021	Quantity agg 2021
Melbourne	VIC	Home Office	1.87	29	49.54	82
Sydney	NSW	Consumer	1.2	17	0.59	33
Sydney	NSW	Corporate	2.69	22	126.38	175
Sydney	NSW	Small Business	2.74	70	36.19	8

• The desired output has the revenue agg and quantity agg separated by year (in the same row). Also, the grouping by city, state and customer type has to be the same in each row.
• Notice that there is no match in the three groupings if one year doesn't have the same data. This means that the following data from 2021 is not included in the desired output:

Data_City	Data_State	Data_Customer Type	Data_Order Year	Revenue agg	Quantity agg
Melbourne	VIC	Corporate	2021	3.61	106
Sydney	NSW	Home Office	2021	4.92	16

You could say that the desired output is some kind of merge between data of both years or something, but I'm not really sure how to proceed. Maybe there is an advanced option while grouping for separating by year but I'm also not sure. 

The desired output is needed for creating new columns that contains calculations between data of both years (given that they have the same groupings), which are alot easier if they are side by side in the same table.

Any suggestion would be deeply appreciated,

Thanks.

[bcdobbs · Accepted solution]

I think you'd be better skipping the calculated table and setting up some measures which you can drop into a table. Have a look at this demo: Revenue Example 

There is a basic measure for both Quantity and Revenue and then versions that filter by year using CALCULATE. That could be made more dynamic.

Lastly there is a measure that checks which rows to include which is put on the visual level filter of the table and set to 1:

[Anonymous]

Thanks @bcdobbs !! That was exactly what I needed.