filters for the row of a matrix

[Pikachu-Power]

hello all,

is it possible to define filters for the rows of a matrix? for example i have following source:

As a value field in the matrix i take CALCULATE( SUM (VALUE)). For the row fields i want DBI with filters Pos2 = A and so on with different filters. Finally I will show DBI, A, E, S, B, C in the rows of a matrix in power bi.

thanks for ideas.

[v-robertq-msft]

Hi, @Pikachu-Power 

Yes, you can transform the data in the Power Query editor to get the data displayed in one Matrix. If you can solve your problem after transforming the data, would you like to share some experience or mark my reply as a solution so that others can learn from it too?

Thanks in advance!

Best Regards,

Community Support Team _Robert Qin

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

[v-robertq-msft · Accepted solution]

Hi, @Pikachu-Power 

According to your description, I think you have to create another table chart to display value for X and Y, you can take a look at my method:

1. I created these measures:

1ST1 = SUMX(FILTER('Table',[KST]=1&&[Layer]="lst"&&[Position5] in {"X","Y"} ),[Value])

PLAN1 = SUMX(FILTER('Table',[KST]=1&&[Layer]="Plan"&&[Position5] in {"X","Y"} ),[Value])

DELTA1STPLAN = [1ST1]-[PLAN1]

2. Then I created another table chart to place these measures and [Position5]:

3. And I change the color of the column header to white to make the column header disappear:

4. Then I drag the two charts together, like this:

And you can get what you want.

You can download my test pbix file here

Best Regards,

Community Support Team _Robert Qin

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

[Pikachu-Power]

Ok thanks but that was also possible with the solution of mparhi.

I think i have to change than the datasource via transformation. See no chance to have one matrix. 

[v-robertq-msft]

Hi, @Pikachu-Power 

According to your picture of expected output, you can try my steps:

1. Create these measures:

1ST = SUMX(FILTER('Table',[KST]=1&&[Layer]="lst"&&[Position4] in {"E","S"} ),[Value])

PLAN = SUMX(FILTER('Table',[KST]=1&&[Layer]="Plan"&&[Position4] in {"E","S"} ),[Value])

DELTA 1ST-PLAN = [1ST]-[PLAN]

2. Create a Matrix and place these measures and [Position4], then change the name of total row, like this:

And you can get what you want.

You can download my test pbix file here

Best Regards,

Community Support Team _Robert Qin

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

[Pikachu-Power]

Hi Robertq,

thank you for your feedback. but is it also possibile to go on with X, Y after E, S in the same column? Or do you have to start a new matrix?

With to many matrices I have gaps. would be nice if it would be possible to let the column header vanish.

[mparhi]

1. Create measures with different filters like 1st and Plan and create a Matrix visual.

IST = CALCULATE(SUM('Table'[Value]),'Table'[Layer]="1st")

2.Use the Position column as a filter for this visual from the Filter pane and select only E and S.

3.Create Matrix for other filters for the Position column similarly.

[Pikachu-Power]

Good idea but i will have 20 rows... it will look strange at the end. And I have no place to use 10 matrices among themselves. And there is no possibility to hide the column titel (just to show one row) right? I think I need an onother solution.

[Fowmy]

@Pikachu-Power 

Can you share some sample data and the expected result to have a clear understanding of your question?
You can save your files in OneDrive, Google Drive, or any other cloud sharing platforms and share the link here.
____________________________________
How to paste sample data with your question?
How to get your questions answered quickly?

_____________________________________
Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube, LinkedIn

[Pikachu-Power]

hello fowmy,

sample data would be to complex and it is senstive data. i tried wo discribe in more detail:

What I want is to use different filters for rows and colums...

for rows E and S for example with KST = 1

for columns value with  Layer = Ist or Plan

If i define one measure for E with all filters I have the problem to display the matrix I want... with Positions in rows. What would be the best approch for that? 

[Pikachu-Power]

Ok I realised that all filters can be used for the column measures at first. But how to show the positions in rows? The positions are in a hierarchie in the datasource but i want to show it in rows with no hierarchie.