cancel
Showing results for
Did you mean:
Helper I

## Help with creating a measure of counterpart

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%
1 ACCEPTED SOLUTION
Solution Specialist

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)"
)``````

9 REPLIES 9
Solution Specialist

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.

Helper I

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!

Solution Specialist

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.

Helper I

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)" )```
Solution Specialist

This is what I get.

Please share details about your model to be able to help.

Helper I

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

Solution Specialist

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)"
)``````

Helper I

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.

Solution Specialist

Most welcome.... my pleasure

Announcements