Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
Thanks for your help, that would be the solution about the model/measures. I had hoped there was a more elegant 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