Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
Solved! Go to 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:
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])
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.
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:
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])
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.
@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
User | Count |
---|---|
84 | |
80 | |
72 | |
71 | |
55 |
User | Count |
---|---|
108 | |
106 | |
93 | |
85 | |
67 |