DAX Query to Calculate % Difference

Question

I have some data that looks like this:

Gender  Salary  PayGrade
M       40000    3
F       35000    3
M       50000    5
M       65000    7

I'm creating a PowerBI visual that should display a horizontal bar for each pay grade, showing the $ difference by gender. I.E. In PayGrade 3, males are paid $5000 more than females in this example.

How can I structure a DAX query to model this? Should be something like

=IF(SUM(MaleSalaries) > SUM(FemaleSalaries),
    SUM(MaleSalaries) - SUM(FemaleSalaries) / COUNT(FemaleSalaries)).

I'm assuming the partitioning by grade will happen in the visualization.

Edit: Here's my DAX queries for the gap, and the gender column:

WageGap = IF(COUNTROWS(VALUES('Ex 6A-Salary Analysis'[Gender])) > 1,
             IF(CALCULATE(AVERAGE('Ex 6A-Salary Analysis'[Annualized Salary]), 'Ex 6A-Salary Analysis'[Gender] = "M") > 
             CALCULATE(AVERAGE('Ex 6A-Salary Analysis'[Annualized Salary]), 'Ex 6A-Salary Analysis'[Gender] = "F"),CALCULATE(AVERAGE('Ex 6A-Salary Analysis'[Annualized Salary]), 'Ex 6A-Salary Analysis'[Gender] = "M") -
             CALCULATE(AVERAGE('Ex 6A-Salary Analysis'[Annualized Salary]), 'Ex 6A-Salary Analysis'[Gender] = "F"),CALCULATE(AVERAGE('Ex 6A-Salary Analysis'[Annualized Salary]), 'Ex 6A-Salary Analysis'[Gender] = "F") -
             CALCULATE(AVERAGE('Ex 6A-Salary Analysis'[Annualized Salary]), 'Ex 6A-Salary Analysis'[Gender] = "M"))
             ,0)


ColumnGender = IF(COUNTROWS(VALUES('Ex 6A-Salary Analysis'[Gender]))>1,IF(CALCULATE(AVERAGE('Ex 6A-Salary Analysis'[Annualized Salary]),'Ex 6A-Salary Analysis'[Gender]="M")>CALCULATE(AVERAGE('Ex 6A-Salary Analysis'[Annualized Salary]),'Ex 6A-Salary Analysis'[Gender]="F"),"M","F"),"")

Alexis Olson Alexis Olson · Accepted Answer · 2017-10-17T22:27:36

If you are looking for the difference in average salaries per PayGrade, I think you would want something like the following:

WageGap = IF(DISTINCTCOUNT(Pay[Gender]) > 1,
             CALCULATE(AVERAGE(Pay[Salary]), Pay[Gender] = "M") - 
             CALCULATE(AVERAGE(Pay[Salary]), Pay[Gender] = "F")
             ,0)

Where Pay is your data table and you have PayGrade on your chart axis.

This measure first tests if there are more than one gender in a certain pay grade and then takes the difference of the average salaries.

Edit:

It looks like you're trying to make this into a tornado chart. To get that to work, you'll need two separate measures that can be defined using the measure I wrote above.

Men = IF([WageGap] > 0, [WageGap], BLANK())
Women = IF([WageGap] < 0, -[WageGap], BLANK())

Try putting those two measures into your values for the visual.

DAX Query to Calculate % Difference

1 Answers