Skip to main content
cancel
Showing results for 
Search instead 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

Reply
CEllis
Resolver I
Resolver I

Matrix Overlapping Groups

 

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

CEllis_0-1718096106731.png

 

 

1 ACCEPTED SOLUTION
v-jtian-msft
Community Support
Community Support

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

vjtianmsft_0-1718178080541.png

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

vjtianmsft_1-1718178094759.png

vjtianmsft_2-1718178102924.png

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])

vjtianmsft_3-1718178148611.png

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

vjtianmsft_4-1718178172733.pngvjtianmsft_5-1718178180754.pngvjtianmsft_6-1718178186905.pngvjtianmsft_7-1718178195417.png

 

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

vjtianmsft_8-1718178265152.png

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

vjtianmsft_9-1718178279712.png

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.

View solution in original post

2 REPLIES 2
v-jtian-msft
Community Support
Community Support

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

vjtianmsft_0-1718178080541.png

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

vjtianmsft_1-1718178094759.png

vjtianmsft_2-1718178102924.png

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])

vjtianmsft_3-1718178148611.png

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

vjtianmsft_4-1718178172733.pngvjtianmsft_5-1718178180754.pngvjtianmsft_6-1718178186905.pngvjtianmsft_7-1718178195417.png

 

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

vjtianmsft_8-1718178265152.png

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

vjtianmsft_9-1718178279712.png

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.

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"

 

CEllis_0-1718287208753.png

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors