cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## Adding a customized column group in table matrix

Is there a way to add a customized column group in table matrix similar to the image below?

The counts are all measures.

2 ACCEPTED SOLUTIONS
Super User

@tomperro this needs you to create a custom table with the intended structure.

col1 with values : selected 6 months period ,  selected 6 month period vs previous 6 montth period.

col2  with values : the name of the measures.

then drag and drop them into the matrix in the columns.

create one measure with switch statement :

switch

true() ,

selectedvalue col2 = measure1 , [ measure1]  ,

selectedvalue col2 = measure2 , [ measure2]  ,

...

let me know if this helps.

Community Support

Hi @tomperro ,

Thanks for the reply from @Daniel29195 .

Here's the example data I created:

 Test Type Column1 Column2 111 1 18 222 2 17 333 3 16 444 4 15 555 5 14 666 6 13 777 7 12 888 8 11 999 9 10

Create a few simple measures:

``````A1 = AVERAGE('Table'[Column1])

A2 = COUNT('Table'[Column2])

B1 = SUM('Table'[Column1])

B2 = SUM('Table'[Column2])``````

Create a table:

``Table 2 = CROSSJOIN(VALUES('Table'[Test Type]),{"Group A", "Group B"})``

Create two measures that serve to group A1 and B1 into Group A and A2 and B2 into Group B.

``````Measure =
SWITCH(
TRUE(),
MAX('Table 2'[Value])="Group A",CALCULATE([A1],FILTER('Table','Table'[Test Type]=MAX('Table 2'[Test Type]))),
MAX('Table 2'[Value])="Group B",CALCULATE([A2],FILTER('Table','Table'[Test Type]=MAX('Table 2'[Test Type])))
)

Measure 2 =
SWITCH(
TRUE(),
MAX('Table 2'[Value])="Group A",CALCULATE([B1],FILTER('Table','Table'[Test Type]=MAX('Table 2'[Test Type]))),
MAX('Table 2'[Value])="Group B",CALCULATE([B2],FILTER('Table','Table'[Test Type]=MAX('Table 2'[Test Type])))
)``````

Using the matrix visual object for display, the final page visualization is shown below:

If this doesn't help you solve the problem. I would be grateful if you could provide me with the pbix file or sample data.

If you have any other questions please feel free to contact me.

The pbix file is attached.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

2 REPLIES 2
Community Support

Hi @tomperro ,

Thanks for the reply from @Daniel29195 .

Here's the example data I created:

 Test Type Column1 Column2 111 1 18 222 2 17 333 3 16 444 4 15 555 5 14 666 6 13 777 7 12 888 8 11 999 9 10

Create a few simple measures:

``````A1 = AVERAGE('Table'[Column1])

A2 = COUNT('Table'[Column2])

B1 = SUM('Table'[Column1])

B2 = SUM('Table'[Column2])``````

Create a table:

``Table 2 = CROSSJOIN(VALUES('Table'[Test Type]),{"Group A", "Group B"})``

Create two measures that serve to group A1 and B1 into Group A and A2 and B2 into Group B.

``````Measure =
SWITCH(
TRUE(),
MAX('Table 2'[Value])="Group A",CALCULATE([A1],FILTER('Table','Table'[Test Type]=MAX('Table 2'[Test Type]))),
MAX('Table 2'[Value])="Group B",CALCULATE([A2],FILTER('Table','Table'[Test Type]=MAX('Table 2'[Test Type])))
)

Measure 2 =
SWITCH(
TRUE(),
MAX('Table 2'[Value])="Group A",CALCULATE([B1],FILTER('Table','Table'[Test Type]=MAX('Table 2'[Test Type]))),
MAX('Table 2'[Value])="Group B",CALCULATE([B2],FILTER('Table','Table'[Test Type]=MAX('Table 2'[Test Type])))
)``````

Using the matrix visual object for display, the final page visualization is shown below:

If this doesn't help you solve the problem. I would be grateful if you could provide me with the pbix file or sample data.

If you have any other questions please feel free to contact me.

The pbix file is attached.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

Super User

@tomperro this needs you to create a custom table with the intended structure.

col1 with values : selected 6 months period ,  selected 6 month period vs previous 6 montth period.

col2  with values : the name of the measures.

then drag and drop them into the matrix in the columns.

create one measure with switch statement :

switch

true() ,

selectedvalue col2 = measure1 , [ measure1]  ,

selectedvalue col2 = measure2 , [ measure2]  ,

...

let me know if this helps.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.