cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
zina_t
Helper I
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 NameCount EECount Recvd% RcvdAVG Change by RaceExpected Results
Race A1717.4%8.5%-1.0%
Race B2845.9%8.5%-2.6%
Race C (Counterpart)5958.5%  
1 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)" 
    )

 

View solution in original post

9 REPLIES 9
Mohammad_Refaei
Solution Specialist
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.

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

Mohammad_Refaei_0-1631559742490.png

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

 

View solution in original post

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

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors