Forum Discussion
Table Matrix Compare Time periods Per Indicators - additional group column headline
Hello,
I am looking for a solution to achieve the following representation or grouping.
I want group column headlines.
I do not want to name each column repeatedly.
This is not what i want:
Can I achieve this only by customizing the model or are there design options
Hi Anonymous ,
I created some data:
Here are the steps you can follow：
1. Create calculated table.
Table 2 = CROSSJOIN( VALUES('Table'[Group]),{"Orders","Order Amount","Customers"})2. Create measure.
2021_Customer = SUMX(FILTER(ALL('Table'),'Table'[Year]=2021&&'Table'[Group]=MAX('Table'[Group])&&'Table'[Group1]="Customer"),[Amount])2022_Customer = SUMX(FILTER(ALL('Table'),'Table'[Year]=2022&&'Table'[Group]=MAX('Table'[Group])&&'Table'[Group1]="Customer"),[Amount])diff_Customer = [2022_Customer] - [2021_Customer]2021_Order = SUMX(FILTER(ALL('Table'),'Table'[Year]=2021&&'Table'[Group]=MAX('Table'[Group])&&'Table'[Group1]="Orders"),[Amount])2022_Order = SUMX(FILTER(ALL('Table'),'Table'[Year]=2022&&'Table'[Group]=MAX('Table'[Group])&&'Table'[Group1]="Orders"),[Amount])diff_Order = [2022_Order] - [2021_Order]2021_Order Amount = SUMX(FILTER(ALL('Table'),'Table'[Year]=2021&&'Table'[Group]=MAX('Table'[Group])&&'Table'[Group1]="Order Amount"),[Amount])2022_Order Amount = SUMX(FILTER(ALL('Table'),'Table'[Year]=2022&&'Table'[Group]=MAX('Table'[Group])&&'Table'[Group1]="Order Amount"),[Amount])diff_OrderAmount = [2022_Order Amount] - [2021_Order Amount]Group1 2021 = SWITCH( TRUE(), MAX('Table 2'[Value])="Orders",CALCULATE([2021_Order],FILTER('Table','Table'[Group]=MAX('Table 2'[Group]))), MAX('Table 2'[Value])="Order Amount",CALCULATE([2021_Order Amount],FILTER('Table','Table'[Group]=MAX('Table 2'[Group]))), MAX('Table 2'[Value])="Customers",CALCULATE([2021_Customer],FILTER('Table','Table'[Group]=MAX('Table 2'[Group]))))Group1 2022 = SWITCH( TRUE(), MAX('Table 2'[Value])="Orders",CALCULATE([2022_Order],FILTER('Table','Table'[Group]=MAX('Table 2'[Group]))), MAX('Table 2'[Value])="Order Amount",CALCULATE([2022_Order Amount],FILTER('Table','Table'[Group]=MAX('Table 2'[Group]))), MAX('Table 2'[Value])="Customers",CALCULATE([2022_Customer],FILTER('Table','Table'[Group]=MAX('Table 2'[Group]))))Diff/Trend = SWITCH( TRUE(), MAX('Table 2'[Value])="Orders",CALCULATE([diff_Order],FILTER('Table','Table'[Group]=MAX('Table 2'[Group]))), MAX('Table 2'[Value])="Order Amount",CALCULATE([diff_OrderAmount],FILTER('Table','Table'[Group]=MAX('Table 2'[Group]))), MAX('Table 2'[Value])="Customers",CALCULATE([diff_Customer],FILTER('Table','Table'[Group]=MAX('Table 2'[Group]))))3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
2 Replies
Hi Anonymous ,
I created some data:
Here are the steps you can follow：
1. Create calculated table.
Table 2 = CROSSJOIN( VALUES('Table'[Group]),{"Orders","Order Amount","Customers"})2. Create measure.
2021_Customer = SUMX(FILTER(ALL('Table'),'Table'[Year]=2021&&'Table'[Group]=MAX('Table'[Group])&&'Table'[Group1]="Customer"),[Amount])2022_Customer = SUMX(FILTER(ALL('Table'),'Table'[Year]=2022&&'Table'[Group]=MAX('Table'[Group])&&'Table'[Group1]="Customer"),[Amount])diff_Customer = [2022_Customer] - [2021_Customer]2021_Order = SUMX(FILTER(ALL('Table'),'Table'[Year]=2021&&'Table'[Group]=MAX('Table'[Group])&&'Table'[Group1]="Orders"),[Amount])2022_Order = SUMX(FILTER(ALL('Table'),'Table'[Year]=2022&&'Table'[Group]=MAX('Table'[Group])&&'Table'[Group1]="Orders"),[Amount])diff_Order = [2022_Order] - [2021_Order]2021_Order Amount = SUMX(FILTER(ALL('Table'),'Table'[Year]=2021&&'Table'[Group]=MAX('Table'[Group])&&'Table'[Group1]="Order Amount"),[Amount])2022_Order Amount = SUMX(FILTER(ALL('Table'),'Table'[Year]=2022&&'Table'[Group]=MAX('Table'[Group])&&'Table'[Group1]="Order Amount"),[Amount])diff_OrderAmount = [2022_Order Amount] - [2021_Order Amount]Group1 2021 = SWITCH( TRUE(), MAX('Table 2'[Value])="Orders",CALCULATE([2021_Order],FILTER('Table','Table'[Group]=MAX('Table 2'[Group]))), MAX('Table 2'[Value])="Order Amount",CALCULATE([2021_Order Amount],FILTER('Table','Table'[Group]=MAX('Table 2'[Group]))), MAX('Table 2'[Value])="Customers",CALCULATE([2021_Customer],FILTER('Table','Table'[Group]=MAX('Table 2'[Group]))))Group1 2022 = SWITCH( TRUE(), MAX('Table 2'[Value])="Orders",CALCULATE([2022_Order],FILTER('Table','Table'[Group]=MAX('Table 2'[Group]))), MAX('Table 2'[Value])="Order Amount",CALCULATE([2022_Order Amount],FILTER('Table','Table'[Group]=MAX('Table 2'[Group]))), MAX('Table 2'[Value])="Customers",CALCULATE([2022_Customer],FILTER('Table','Table'[Group]=MAX('Table 2'[Group]))))Diff/Trend = SWITCH( TRUE(), MAX('Table 2'[Value])="Orders",CALCULATE([diff_Order],FILTER('Table','Table'[Group]=MAX('Table 2'[Group]))), MAX('Table 2'[Value])="Order Amount",CALCULATE([diff_OrderAmount],FILTER('Table','Table'[Group]=MAX('Table 2'[Group]))), MAX('Table 2'[Value])="Customers",CALCULATE([diff_Customer],FILTER('Table','Table'[Group]=MAX('Table 2'[Group]))))3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly