Help with creating a measure of counterpart

[Anonymous]

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%

[Mohammad_Refaei]

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.

[Anonymous]

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!

[Mohammad_Refaei]

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.

[Anonymous]

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

[Mohammad_Refaei]

This is what I get.

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

[Anonymous]

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

[Mohammad_Refaei · Accepted 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)" 
    )

[Anonymous]

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.

[Mohammad_Refaei]

Most welcome.... my pleasure