Forum Discussion

hylosko

Helper III

4 years ago

Solved

Colors in matrix columns

It is possible to color matrix like here in me excel ?

Need Help

Anonymous

4 years ago

Hi hylosko ,

I created some data:

Here are the steps you can follow：

1. Use Enter data to create a table..

2. Create measure.

T1_CAT1_Measure =
SUMX(
    FILTER(ALL('Table'),
    'Table'[Type]="Type1"&&'Table'[Group]="CAT1"),[Amount])

T1_CAT2_Measure =
SUMX(
    FILTER(ALL('Table'),
    'Table'[Type]="Type1"&&'Table'[Group]="CAT2"),[Amount])

T1_CAT3_Measure =
SUMX(
    FILTER(ALL('Table'),
    'Table'[Type]="Type1"&&'Table'[Group]="CAT3"),[Amount])

T1_CAT4_Measure =
SUMX(
    FILTER(ALL('Table'),
    'Table'[Type]="Type1"&&'Table'[Group]="CAT4"),[Amount])

T1_CAT5_Measure =
SUMX(
    FILTER(ALL('Table'),
    'Table'[Type]="Type1"&&'Table'[Group]="CAT5"),[Amount])

T1_CAT6_Measure =
SUMX(
    FILTER(ALL('Table'),
    'Table'[Type]="Type1"&&'Table'[Group]="CAT6"),[Amount])

T2_CAT1_Measure =
SUMX(
    FILTER(ALL('Table'),
    'Table'[Type]="Type2"&&'Table'[Group]="CAT1"),[Amount])

T2_CAT2_Measure =
SUMX(
    FILTER(ALL('Table'),
    'Table'[Type]="Type2"&&'Table'[Group]="CAT2"),[Amount])

T2_CAT3_Measure =
SUMX(
    FILTER(ALL('Table'),
    'Table'[Type]="Type2"&&'Table'[Group]="CAT3"),[Amount])

T2_CAT3_Measure =
SUMX(
    FILTER(ALL('Table'),
    'Table'[Type]="Type2"&&'Table'[Group]="CAT3"),[Amount])

T2_CAT4_Measure =
SUMX(
    FILTER(ALL('Table'),
    'Table'[Type]="Type2"&&'Table'[Group]="CAT4"),[Amount])

T2_CAT5_Measure =
SUMX(
    FILTER(ALL('Table'),
    'Table'[Type]="Type2"&&'Table'[Group]="CAT5"),[Amount])

T2_CAT6_Measure =
SUMX(
    FILTER(ALL('Table'),
    'Table'[Type]="Type2"&&'Table'[Group]="CAT6"),[Amount])

CAT1 M =
SWITCH(
    TRUE(),
    MAX('Table2'[Type])="Type1",CALCULATE([T1_CAT1_Measure],
    FILTER('Table','Table'[Group2]=MAX('Table2'[Group2]))),
    MAX('Table2'[Type])="Type2",CALCULATE([T2_CAT1_Measure],
    FILTER('Table','Table'[Group2]=MAX('Table2'[Group2]))))

CAT2 M =
SWITCH(
    TRUE(),
    MAX('Table2'[Type])="Type1",CALCULATE([T1_CAT2_Measure],
    FILTER('Table','Table'[Group2]=MAX('Table2'[Group2]))),
    MAX('Table2'[Type])="Type2",CALCULATE([T2_CAT2_Measure],
    FILTER('Table','Table'[Group2]=MAX('Table2'[Group2]))))

CAT3 M =
SWITCH(
    TRUE(),
    MAX('Table2'[Type])="Type1",CALCULATE([T1_CAT3_Measure],
    FILTER('Table','Table'[Group2]=MAX('Table2'[Group2]))),
    MAX('Table2'[Type])="Type2",CALCULATE([T2_CAT3_Measure],
    FILTER('Table','Table'[Group2]=MAX('Table2'[Group2]))))

CAT4 M =
SWITCH(
    TRUE(),
    MAX('Table2'[Type])="Type1",CALCULATE([T1_CAT4_Measure],
    FILTER('Table','Table'[Group2]=MAX('Table2'[Group2]))),
    MAX('Table2'[Type])="Type2",CALCULATE([T2_CAT4_Measure],
    FILTER('Table','Table'[Group2]=MAX('Table2'[Group2]))))

CAT5 M =
SWITCH(
    TRUE(),
    MAX('Table2'[Type])="Type1",CALCULATE([T1_CAT5_Measure],
    FILTER('Table','Table'[Group2]=MAX('Table2'[Group2]))),
    MAX('Table2'[Type])="Type2",CALCULATE([T2_CAT5_Measure],
    FILTER('Table','Table'[Group2]=MAX('Table2'[Group2]))))

CAT6 M =
SWITCH(
    TRUE(),
    MAX('Table2'[Type])="Type1",CALCULATE([T1_CAT6_Measure],
    FILTER('Table','Table'[Group2]=MAX('Table2'[Group2]))),
    MAX('Table2'[Type])="Type2",CALCULATE([T2_CAT6_Measure],
    FILTER('Table','Table'[Group2]=MAX('Table2'[Group2]))))

3. Create calculated column.

Color =
IF(
    'Table2'[Type]="Type1","yellow",
"blue")

4. Open the matrix and place the calculated columns and measures as follows.

5. Open the format of the matrix – Cell elements – Series select each measure – open the Background dolor – fx.

Here [CAT1 M] is the column - click fx of Background color

Enter the Background color interface

Format style – Field value

What field should we base this on - select [Color] of Table2

[CAT1 M], [CAT2 M], [CAT3 M], [CAT4 M], [CAT5 M], [CAT6 M] do this

6. Result:

If you need pbix, please click here.

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

6 Replies

Fowmy
Super User
4 years ago
hylosko

You can do it using conditional formatting, first cleate a meaure like

CF = SWITCH( SELECTEDVALUE( 'table'[types]), "Type1" , "yellow", "Type2" , "light blue" )

Select the Background option under Cell Element property of the matrix and apply conditional formatting by Field value then choose the above measures.

Anonymous

Not applicable

4 years ago

Hi hylosko ,

I created some data:

Here are the steps you can follow：

1. Use Enter data to create a table..

2. Create measure.

T1_CAT1_Measure =
SUMX(
    FILTER(ALL('Table'),
    'Table'[Type]="Type1"&&'Table'[Group]="CAT1"),[Amount])

T1_CAT2_Measure =
SUMX(
    FILTER(ALL('Table'),
    'Table'[Type]="Type1"&&'Table'[Group]="CAT2"),[Amount])

T1_CAT3_Measure =
SUMX(
    FILTER(ALL('Table'),
    'Table'[Type]="Type1"&&'Table'[Group]="CAT3"),[Amount])

T1_CAT4_Measure =
SUMX(
    FILTER(ALL('Table'),
    'Table'[Type]="Type1"&&'Table'[Group]="CAT4"),[Amount])

T1_CAT5_Measure =
SUMX(
    FILTER(ALL('Table'),
    'Table'[Type]="Type1"&&'Table'[Group]="CAT5"),[Amount])

T1_CAT6_Measure =
SUMX(
    FILTER(ALL('Table'),
    'Table'[Type]="Type1"&&'Table'[Group]="CAT6"),[Amount])

T2_CAT1_Measure =
SUMX(
    FILTER(ALL('Table'),
    'Table'[Type]="Type2"&&'Table'[Group]="CAT1"),[Amount])

T2_CAT2_Measure =
SUMX(
    FILTER(ALL('Table'),
    'Table'[Type]="Type2"&&'Table'[Group]="CAT2"),[Amount])

T2_CAT3_Measure =
SUMX(
    FILTER(ALL('Table'),
    'Table'[Type]="Type2"&&'Table'[Group]="CAT3"),[Amount])

T2_CAT3_Measure =
SUMX(
    FILTER(ALL('Table'),
    'Table'[Type]="Type2"&&'Table'[Group]="CAT3"),[Amount])

T2_CAT4_Measure =
SUMX(
    FILTER(ALL('Table'),
    'Table'[Type]="Type2"&&'Table'[Group]="CAT4"),[Amount])

T2_CAT5_Measure =
SUMX(
    FILTER(ALL('Table'),
    'Table'[Type]="Type2"&&'Table'[Group]="CAT5"),[Amount])

T2_CAT6_Measure =
SUMX(
    FILTER(ALL('Table'),
    'Table'[Type]="Type2"&&'Table'[Group]="CAT6"),[Amount])

CAT1 M =
SWITCH(
    TRUE(),
    MAX('Table2'[Type])="Type1",CALCULATE([T1_CAT1_Measure],
    FILTER('Table','Table'[Group2]=MAX('Table2'[Group2]))),
    MAX('Table2'[Type])="Type2",CALCULATE([T2_CAT1_Measure],
    FILTER('Table','Table'[Group2]=MAX('Table2'[Group2]))))

CAT2 M =
SWITCH(
    TRUE(),
    MAX('Table2'[Type])="Type1",CALCULATE([T1_CAT2_Measure],
    FILTER('Table','Table'[Group2]=MAX('Table2'[Group2]))),
    MAX('Table2'[Type])="Type2",CALCULATE([T2_CAT2_Measure],
    FILTER('Table','Table'[Group2]=MAX('Table2'[Group2]))))

CAT3 M =
SWITCH(
    TRUE(),
    MAX('Table2'[Type])="Type1",CALCULATE([T1_CAT3_Measure],
    FILTER('Table','Table'[Group2]=MAX('Table2'[Group2]))),
    MAX('Table2'[Type])="Type2",CALCULATE([T2_CAT3_Measure],
    FILTER('Table','Table'[Group2]=MAX('Table2'[Group2]))))

CAT4 M =
SWITCH(
    TRUE(),
    MAX('Table2'[Type])="Type1",CALCULATE([T1_CAT4_Measure],
    FILTER('Table','Table'[Group2]=MAX('Table2'[Group2]))),
    MAX('Table2'[Type])="Type2",CALCULATE([T2_CAT4_Measure],
    FILTER('Table','Table'[Group2]=MAX('Table2'[Group2]))))

CAT5 M =
SWITCH(
    TRUE(),
    MAX('Table2'[Type])="Type1",CALCULATE([T1_CAT5_Measure],
    FILTER('Table','Table'[Group2]=MAX('Table2'[Group2]))),
    MAX('Table2'[Type])="Type2",CALCULATE([T2_CAT5_Measure],
    FILTER('Table','Table'[Group2]=MAX('Table2'[Group2]))))

CAT6 M =
SWITCH(
    TRUE(),
    MAX('Table2'[Type])="Type1",CALCULATE([T1_CAT6_Measure],
    FILTER('Table','Table'[Group2]=MAX('Table2'[Group2]))),
    MAX('Table2'[Type])="Type2",CALCULATE([T2_CAT6_Measure],
    FILTER('Table','Table'[Group2]=MAX('Table2'[Group2]))))

3. Create calculated column.

Color =
IF(
    'Table2'[Type]="Type1","yellow",
"blue")

4. Open the matrix and place the calculated columns and measures as follows.

5. Open the format of the matrix – Cell elements – Series select each measure – open the Background dolor – fx.

Here [CAT1 M] is the column - click fx of Background color

Enter the Background color interface

Format style – Field value

What field should we base this on - select [Color] of Table2

[CAT1 M], [CAT2 M], [CAT3 M], [CAT4 M], [CAT5 M], [CAT6 M] do this

6. Result:

If you need pbix, please click here.

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

hylosko
Helper III
4 years ago
I tried your method, but it is not working as well
- Fowmy
  Super User
  4 years ago
  hylosko
  
  If you could show/share how you tried the suggested solution, then I will be able to understand
  - hylosko
    Helper III
    4 years ago
    I will try to explain,
    so
    
    in my matrix in rows i have s-type column (which give me s1,s2,s,3 etc), in columns i have attribute called type (which give me many types for example type1,type2) and in values i have category (cat1,cat2,cat3). That how my matrix is builded. I cant share you full details beacuse its very confidental data also connected to our org datasets (its is imposible to past here anything) 😞 When i tried your suggested solution, it not colored anything

Forum Discussion

Colors in matrix columns

6 Replies

Related content

Matrix Column Widths

Matrix column based on other matrix columns

Matrix column not grouped

Color matrix by column total

Matrix table column Hide