Filter for a part of the matrix

[Maria929292]

Hi everyone,

I have 2 tables joined with one-to-many realtionships: one has an actual number of contracts, another has a target number. A matrix in the report looks like this:

"Fact type 1" and "fact all types" are from the same table.

I also have a filter by subtypes as every type has several subtypes.

Basically, I need to see

- contracts type 1/all actual contracts ratio

- contracts type 1/target number of contracts ratio

and the same with subtypes:

- contracts type 1(subtype 1)/all actual contracts ratio

- contracts type 1(subtype 1)/target number of contracts ratio

Obviously when I use filter by subtypes I get this:

It filters down values in all columns to the chosen subtype, but what I need is for "fact all types" and "target all types" remain unchanged and only "fact type " filtered down to the chosen subtype, like this: 

Could you help me with the way to achieve it, please?

Thank you.

[v-robertq-msft · Accepted solution]

Hi, @Maria929292 

According to your description and sample pictures, I created some data to reproduced the problem you encountered in the Matrix. I think you can use measures to achieve your requirement. You can take a look at my method and find if it’s useful:

1. I created the sample data and created the relationship according to your description:

2. Then I created these measures to place them into the Matrix:

fact type 2 =

COUNT('Fact'[fact type 1])

Fact all types1 =

SUMX(FILTER(ALL('Fact'),[Country]=MAX([Country])),[Fact all types])

Target all types1 =

CALCULATE(SUM(Target[Target all types]),FILTER(ALL('Fact'),[Country]=MAX([Country])))

% type 1 out of all types =

DIVIDE([fact type 2],[Fact all types1])

% target type 1 out of all types =

DIVIDE([fact type 2],[Target all types1])

3. Create a Slicer and place [subtype] then a Matrix and place it 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.

[amitchandak]

@Maria929292 , use all for selected column or removefilters

calculate([Measure], all(Table[Col1]))

refer

https://www.linkedin.com/pulse/five-recent-power-bi-functions-you-should-use-more-often-amit-chandak