Create a matrix that each row use different filter

[Anonymous]

Hi,

I'm trying to create a matrix table that needs to have a different filter for each segment of the matrix.

For example,

A, B, C, D are from the same column in a reference table and they are also using the same column as value, but the matrix we need to create requires each of A, B, C, D to use a different filter. Is there any possible solution to have this matrix created?

	Jan	Feb	March
A1
A2
A3
A Sub Total
B1
B2
B3
B Sub Total
C1
C2
C3
C Sub Total
D1
D2
D3
D Sub Total
Grand Total

Thank you in advance.

[Anonymous · Accepted solution]

Hi @Anonymous ,

1.My sample data is this.

Category	Sub	Month	Value
A	A1	Jan	1
A	A2	Feb	2
A	A3	Mar	3
B	B1	Jan	4
B	B2	Feb	5
B	B3	Mar	6
C	C1	Jan	7
C	C2	Feb	8
C	C3	Mar	9
D	D1	Jan	1
D	D2	Feb	2
D	D3	Mar	3

2.Create four new tables by entering data. There is no relationship among five tables.

3.Create a measure.

Values = 
IF (
    ISFILTERED ( A[Sub] )
        && MAX ( 'Table'[Sub] ) IN ALLSELECTED ( 'A'[Sub] ),
    SUM ( 'Table'[Value] ),
    IF (
        ISFILTERED ( B[Sub] )
            && MAX ( 'Table'[Sub] ) IN ALLSELECTED ( 'B'[Sub] ),
        SUM ( 'Table'[Value] ),
        IF (
            ISFILTERED ( C[Sub] )
                && MAX ( 'Table'[Sub] ) IN ALLSELECTED ( 'C'[Sub] ),
            SUM ( 'Table'[Value] ),
            IF (
                ISFILTERED ( D[Sub] )
                    && MAX ( 'Table'[Sub] ) IN ALLSELECTED ( 'D'[Sub] ),
                SUM ( 'Table'[Value] )
            )
        )
    )
)

4.The result is as follows.

You can check more details from here.

Best Regards,

Stephen Tao

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

[Greg_Deckler]

@Anonymous Not sure, but perhaps you need something like MM3TR&R - https://community.powerbi.com/t5/Quick-Measures-Gallery/Matrix-Measure-Total-Triple-Threat-Rock-amp-Roll/m-p/411443#M150

ISINSCOPE is your friend.

[amitchandak]

@Anonymous , not very clear. Can you share sample data and sample output in a table format? Or a sample pbix after removing sensitive data.

Now you can check the value in measure and change calculation or filter example

New measure  =

Switch( True(),

Max(Table[Categorty]) ="A" , [Measure],

Max(Table[Categorty]) ="B" , [Measure1],

[Measure3]

)

Refer by HR blog where I moved filter-based  is filtered

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

isinscope - change calculation based on level

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/