Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have below table that I need to create a measure which is as below:
%Gap = % Rcvd - Avg Change by Race (but only considering Race C) or I can say it in a different way as below:
%Gap = %Rcvd (Race A=7.4%) - %Rcvd (Race C=8.5%) = -1.0%
%Gap = %Rcvd (Race B=5.9%) - %Rcvd (Race C=8.5%) = -2.6%
How can I create a measure that can get me the results as in "Expected Results" column?
Race Name | Count EE | Count Recvd | % Rcvd | AVG Change by Race | Expected Results |
Race A | 17 | 1 | 7.4% | 8.5% | -1.0% |
Race B | 28 | 4 | 5.9% | 8.5% | -2.6% |
Race C (Counterpart) | 59 | 5 | 8.5% |
Solved! Go to Solution.
To have better support from the community in future posts it is advised to share details about your model.
The model here refers to your various table structure, relationships and sample data at least of the main tables.
Try this:
%Gap =
[% Rcvd]
- CALCULATE (
[% Rcvd],
A_Table[Race Name] = "Race C (Counterpart)"
)
Add a calculated column:
%Gap =
[% Rcvd]
- CALCULATE (
MAX ( Data[% Rcvd] ),
FILTER ( ALL ( Data ), Data[Race Name] = "Race C (Counterpart)" )
)
Replace "Data" with your table name.
Hi,
Thank you for your help. I didn't get the same expected result which 8.5%. I need to highlight that % Rcvd is a measure. And I'm wondering why you used MAX and what I need is Average of this measure.
I'm sorry if I wasn't that clear, but hope you could help and understood what I exactly want.
Thank you!
Well, you can try this:
%Gap Measure =
[% Rcvd]
- CALCULATE (
AVERAGE ( [% Rcvd] ),
FILTER ( ALL ( Data ), Data[Race Name] = "Race C (Counterpart)" )
)
If it is not workinig for any reason then please share more details about your model in otrder to avoid any confusion.
The result of below part is 3.5% not 8.5%, did you try it yourself?
CALCULATE ( AVERAGE ( [% Rcvd] ), FILTER ( ALL ( Data ), Data[Race Name] = "Race C (Counterpart)" )
This is what I get.
Please share details about your model to be able to help.
Here's some more details about my measures:
% Rcvd = CALCULATE( AVERAGE(A_Table[Base Pay Change Percent]), FILTER(A_Table,A_Table[Promotion Given (Y/N)]="Yes")
And when to adjust the measure to below:
%Gap Measure = [% Rcvd]
- CALCULATE( [% Rcvd],
FILTER(ALL(A_Table),A_Table[Mapped Race] = "Race C (Counterpart)") )
I got 1.2% for Race A and -0.3% for Race B. I removed the average before % Rcvd as the measure is already AVG
To have better support from the community in future posts it is advised to share details about your model.
The model here refers to your various table structure, relationships and sample data at least of the main tables.
Try this:
%Gap =
[% Rcvd]
- CALCULATE (
[% Rcvd],
A_Table[Race Name] = "Race C (Counterpart)"
)
Thank you for being patient and walking me through the best solution, I really appreciate your kind patient and support. I'm kinda new here and in learning process.
Most welcome.... my pleasure
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |