Matrix Overlapping Groups

[CEllis]

I am trying to combine 2 groups that are in a matrix and add them to the same matrix.

I can get them to appear in seprate matrixies but cant figure out how to get them all into 1 using a dax measure so that the groups appear in the rows.

This is from a single table.
The groups are created using the DAX below.

IF(('Table1'[_Field1]-'Table1'[_Field2])>=30,"Less than expected",
IF(AND(('Table1'[_Field1]-'Table1'[_Field2])<30,
        ('Table1'[_Field1]-'Table1'[_Field2])>=-35),"Expected Progress",
        IF(('Table1'[_Field1]-'Table1'[_Field2])<-35,"Excellent Progress","")))

Example matrix

[v-jtian-msft · Accepted solution]

Hi,@CEllis
I am glad to help you.
 

According to your description, you want to merge two matrices into one (even though one of the matrices uses data created based on some of the data in the original matrix, and there is no data in the original data for the second matrix that has the aggregated data)

If my understanding is correct, you can refer to my test below, since I don't have your actual data, I created some simple data, but the idea is the same, hope it helps you

Here is my test data

Based on the DAX code you provided I created the type categorization

C_level = 
IF(('Table1'[_Field1]-'Table1'[_Field2])>=30,"Less than expected",
IF(AND(('Table1'[_Field1]-'Table1'[_Field2])<30,
        ('Table1'[_Field1]-'Table1'[_Field2])>=-35),"Expected Progress",
        IF(('Table1'[_Field1]-'Table1'[_Field2])<-35,"Excellent Progress","")))

C_level2 = 
IF('Table1'[C_level]="Excellent Progress"||'Table1'[C_level]="Expected Progress","Excellent Progress & Expected Progress",[C_level])

In this matrix, the columns “Excellent Progress & Expected Progress” do not exist in the original data.

But you can merge the two matrices by creating a new column table.

I have created a "Header" table

M_Num = 
SWITCH(TRUE(),
    SELECTEDVALUE(Header[category])="Material",
        SWITCH(TRUE(),
            SELECTEDVALUE('Header'[level])="Excellent Progress",[M_1],
            SELECTEDVALUE(Header[level])="Expected Progress",[M_1],
            SELECTEDVALUE(Header[level])="Less than expected",[M_1]
        ),
    SELECTEDVALUE(Header[category])="Others",
    
        SWITCH(TRUE(),
            SELECTEDVALUE(Header[level])="Excellent Progress & Expected Progress",[M_2]
        )
)

M_1 = 
CALCULATE(SUM('Table1'[_Field1]),FILTER(ALL('Table1'),'Table1'[C_level]=MAX('Header'[level])))

M_2 = 
VAR sum_ = CALCULATE(SUM(Table1[_Field1]),FILTER(ALL(Table1),'Table1'[C_level2]="Excellent Progress & Expected Progress"))
RETURN sum_

In fact, you can control the display of the results by sorting them into categories, depending on the names of the columns displayed in the matrix.

You can control the display of different calculations by categorizing them (I recommend using measure).
suggestion2

If you don't need to display the summary value of total separately after merging two matrices, you may consider using the isIncope() function to dynamically modify the values in the matrix.

like this.

M_isIncope = 
IF(
    ISINSCOPE(Table1[C_level]),1,
    2)

In this way you can artificially modify the values in the matrix by performing different measurements in it through judgments

Artificially modifying the value of total in the settings of the matrix visual

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[CEllis]

Fantastic @v-jtian-msft much appreciate your support.

The % I use from a field I distinct count in another table becuase I cant get the numbers to change to % when choosing 'show as % of grand total' they all go (blank).

The only thing left is to get the % to appear for 'Excellent & Expected Progress"