Table Matrix Compare Time periods Per Indicators - additional group column headline

[Anonymous]

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

[Anonymous]

Thanks for your help, that would be the solution about the model/measures. I had hoped there was a more elegant solution.

[Anonymous · Accepted solution]

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