Colors in matrix columns

[hylosko]

Hi 

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

[Anonymous · Accepted solution]

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]

I tried your method, but it is not working as well 

[Fowmy]

@hylosko 

If you could show/share how you tried the suggested solution, then I will be able to understand 

[hylosko]

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 

[hylosko]

[Fowmy]

@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.