Total mean and median

DAX DAX Calculations
1

Hi everyone,

Attached is my Gender Pay Gap dashboard that I have just put together. Given the recent discussion about have proper totals, I have doubts about the totals I get in my report and need your help.

The report looks at the Median and Mean of salaries across the four quartiles. Given the small differences in each quartiles, I dont believe the total gap should be as much as it appears on the report.

I would be grateful if you could test my DAX calculation for Median and Mean and then let me know I can get the appropriate Totals.

Thank you,

Gender Pay Report.pbix (148.7 KB)

2

Hi Jawed,

Let me know if this one is of any help to you regarding the incorrect total issue you have.

3

Hi Jawed,

Your formulae are correct. The reason your totals aren’t jiving in your head is because you’re also comparing the different quartiles in your tables. To put it plainly, your total has a comparison between the highest paid person of each gender and the lowest paid. For these visualizations, I’d get rid of the total lines on all 3. I’d also put in a comparison for full time employees versus part time employees so that you’re comparing apples to apples. I saw that you normalized the incomes, but I highly doubt that some of your part time folks would be paid as much as your full time folks if they worked full-time hours.

4

Hello Jawed

Sam’s Youtube video attached does the trick.
By applying its principle, you could get to something like this (for median females):

 Median Female = 
    VAR MedianFiltered = 
            CALCULATE(
                [Median],
               Data[Gender] = "Female"
            )
VAR MedianTotal = 
    MEDIANX(
        FILTER(
            SUMMARIZE( 'Data', 'Data'[Gender], 'Data'[FTE Salary], "Median", [Median] ),
            'Data'[Gender] = "Female"
        ),
       'Data'[FTE Salary]
    )
RETURN
    IF(
        ISINSCOPE( 'Data'[Quartiles] ),
        MedianFiltered,
        MedianTotal
    )

The same applies to males (replace gender) and to the means.
I’m not happy though by introducing two iterators… potentially there’s a better way of achieving the same.
I attach the PowerBI model, with the two medians computed; to calculate the means, simply replace MEDIANX() by MEANX().
Very nice dashboard, by the way.

Regards

Doru

Gender Pay Report.pbix (154.2 KB)

3 Likes
5

Thank you, @mickeydjw. In terms of comparison, even if I get rid of the quartiles, I should, in theory, get a much smaller difference. In terms of FTE vs. Part Time, what I am comparing here is the FTE rate rather than actual take home. However, if I were to compare the actual take home, your suggestion would be the correct way.

6

Thank you @dimbroane. I did look at model you sent and the results are the same as mine. Albeit through a much thorough process. Looks like that might be the correct result then…

7

Correction. @dimbroane, your version actually gives a much accurate Mean and Median. However, the challenge now is to find a way to combine them as it is hard to run in on charts if they are broken down into Male and Female. So, is there anyone who can suggest a way to achieve the same result but without having to break the DAX down?

8

Hi Jawed,

I’m not suggesting getting rid of the quartiles. That’s actually the data you want. I’m suggesting removing the total line, because it’s misleading. It’s implying that there is a greater gender-wage gap by not taking into account the different quartiles, or pay bands. The group that makes the most money has 2x the males versus the females, whereas the group making the least has 6.3x females to males.

Even if I didn’t have the salary numbers in front of me in your report, I can tell you that the totals based on those multipliers is going to look like gender based inequality, when in reality it’s role-based inequality.

9

Hello

Could you try this formula:

    Median Combined = 
    MEDIANX(
        SUMMARIZE( 'Data', 'Data'[Gender], 'Data'[FTE Salary], "Median", [Median] ),
        'Data'[FTE Salary]
    )
10

I agree. I am going to be taking it out for my Dashboard.

11

Thank you, @dimbroane. This one now seems to be working. I called this one Median and used the underlying DAX within this one instead of referring to “[Median]” to created fewer measures.

12

You are welcome. Happy to have helped.

13

To the contributors of this post, thank you for all your inputs on this topic we are now tagging it as Solved. To help us learn more about your experience in the forum, please take a moment to answer this short forum survey. We appreciate all your help and suggestions. Thanks!

14

Hello All,

Just wonder, if we have to add in special condition in this paygap report, how/what approach should i look at?
Say i have an Attribute with “Acting Position” and “Substantive Position” within a different time frame for each employees, how can i generate the result? or a filter that can identify these two situation?
Any suggestion is highly appreciated!