Problem with DAX formula (average of divided medians caluclated by conditions)

[n00ne]

Hello,

I'm new in DAX formulas and this one I am trying to figure out for few days now.

Below table is an example of how I got data structured.

What I am trying to achieve is explained by below picture:

The end result (marked in yellow) is to have average from divided medians for each position and for each gender.

Ideally would be also to have median calculated when there is at least 2 orrucances of each gender in respective position. Which basically means that in above example median for gender M should not be calculated, hence end result will be like below:

This would be the ideal situation, but I am not sure if this is possible.

Thank you very much for all your help on this.

[n00ne · Accepted solution]

@tamerj1 

Sure I can. Below you can find results for only first date

But of course same should apply to all dates. Basically in data we can see that for position B there is just one M in 1/1/2021 so that should not be taken into consideration. So the average is done only for 2 positions then for respective time.

[tamerj1]

Hi @n00ne 
One way to do that is by utilizing the column total to disply the F/M ratio.
Here is a sample file with the proposed solution https://we.tl/t-KF12DiUFOH

Median Salary = 
VAR MedianSalary = 
    MEDIAN ( Salaries[Salary] )
VAR M_FRation =
    AVERAGEX ( 
        SUMMARIZE ( Salaries, Salaries[Position], Salaries[Gender] ),
        CALCULATE ( 
            DIVIDE ( 
                CALCULATE ( MEDIAN ( Salaries[Salary] ), Salaries[Gender] = "F" ), 
                CALCULATE ( MEDIAN ( Salaries[Salary] ), Salaries[Gender] = "M" ) 
            ) 
        )
    )
RETURN
    IF ( 
        HASONEVALUE ( Salaries[Gender] ),
        FORMAT ( MedianSalary, "#" ),
        M_FRation
    )

[n00ne]

Hi @tamerj1 ,

The solution is great. Works perfect!

Just one question. Is the a way to adjust the formula to check if there are at least 2 occurances of each gender for respective position before calculating divide?

[tamerj1]

@n00ne 
Can you clarify further perhaps using an example?

[n00ne · Accepted solution]

@tamerj1 

Sure I can. Below you can find results for only first date

But of course same should apply to all dates. Basically in data we can see that for position B there is just one M in 1/1/2021 so that should not be taken into consideration. So the average is done only for 2 positions then for respective time.

[tamerj1 · Accepted solution]

Hi @n00ne 
Code is updated as requested https://we.tl/t-kdg7GY1PmH

Median Salary = 
VAR NumOf_F1 = CALCULATE ( COUNTROWS ( Salaries ), Salaries[Gender] = "F" )
VAR NumOf_M1 = CALCULATE ( COUNTROWS ( Salaries ), Salaries[Gender] = "M" )
VAR MedianSalary = IF ( NumOf_F1 >= 2 && NumOf_M1 >= 2, MEDIAN ( Salaries[Salary] ) )
VAR M_FRation =
    AVERAGEX ( 
        SUMMARIZE ( Salaries, Salaries[Position], Salaries[Date] ),
        VAR NumOf_F2 = CALCULATE ( COUNTROWS ( Salaries ), Salaries[Gender] = "F" )
        VAR NumOf_M2 = CALCULATE ( COUNTROWS ( Salaries ), Salaries[Gender] = "M" )
        RETURN
            CALCULATE (
                IF ( 
                    NumOf_F2 >= 2 && NumOf_M2 >= 2,
                    DIVIDE ( 
                        CALCULATE ( MEDIAN ( Salaries[Salary] ), Salaries[Gender] = "F" ), 
                        CALCULATE ( MEDIAN ( Salaries[Salary] ), Salaries[Gender] = "M" ) 
                    ) 
                )
            )
    )
VAR Result =
    IF ( 
        HASONEVALUE ( Salaries[Gender] ),
        FORMAT ( MedianSalary, "#" ),
        M_FRation
    )
RETURN
    Result

[n00ne]

Hi @tamerj1 ,

Thank you for helping me last year with the query.

Now I have to amend it a little bit and it's tricky.

The result of the div should be multiply by number of employees (M+F) and divided by the number of all employees which pass the condition (kind of sumproduct). Graphic extension for the above table (columns are indexed for better visibility of calculations):

The result I need to achieve with the measue is the number 149%.

Please let me know if you are able to support me here.

Thank you very much in advance.

BR,

n00ne

[n00ne]

Hi @BeaBF ,

Thank you for your prompt response.

We don't have averages in the table but medians. Average equation is done only at the end (average from divided medians).

[BeaBF]

@n00ne Ok, so it is the same but with median, change the measure with:

MEDIAN_SALARY = CALCULATE(MEDIAN(Tabella[Salary]), Tabella[Gender] = "F") / CALCULATE(MEDIAN(Tabella[Salary]), Tabella[Gender] = "M")

BF

[n00ne]

Hi @BeaBF ,

This would work only when in rows we will be having positions. I would need DAX formula doing the same as yours but when Position dimention is not in the visual. At the end I will need to have just the average value.

[BeaBF]

@n00ne 

MEDIAN_SALARY = CALCULATE(MEDIAN(Tabella[Salary]), Tabella[Gender] = "F", ALLEXCEPT(Tabella, Tabella[Position])) / CALCULATE(MEDIAN(Tabella[Salary]), Tabella[Gender] = "M", ALLEXCEPT(Tabella, Tabella[Position]))

BF

[n00ne]

Hi @BeaBF ,

I am not getting the same results using above formula as I am expecting.

When positions are in place row level calculations are allright.

When I am left with your calculation only I am getting value 1.07 where I would need averages from divides which is 1.01.

I also assume that adding to that measure condition that divide should be done only for positions when there at least 2 women and 2 men is problematic?

[BeaBF]

@n00ne you can't have the result partitioned for position if you avoid to put the field in the table, because, by nature, the data resulting from the measurement are grouped.

BF

[n00ne]

Hi @BeaBF ,

Any idea how I can get the needed result?

The result visualisation is not a table but line graph.

[BeaBF]

@n00ne paste me a sample plis.

BF

[n00ne]

@BeaBF ,

Here you go:

Line chart will be the end result of this task.

[BeaBF]

@n00ne ok, can you resend me this table with the correct field Date, to give you the same result?
Position Gender Salary
A F 10000
A F 6000
A M 7000
A M 10000
A M 7000
B F 6000
B F 4000
B M 6000
C F 10000
C F 9000
C M 9000
C M 9000

thx,

BF

[n00ne]

Hi @BeaBF ,

Please find under link excel file:

https://www.dropbox.com/scl/fi/bu334hhwdhdma93lrexvu/example.xlsx?dl=0&rlkey=3t28m1e0rkicalsg7a60b4m...

[BeaBF]

@n00ne SOrry, i can't open the file...

paste here the date column plis 🙂

THX,

BF

[n00ne]

@BeaBF 

Position Gender Salary Date
A F 10000 01/01/2021
A F 6000 01/01/2021
A M 7000 01/01/2021
A M 10000 01/01/2021
A M 7000 01/01/2021
B F 6000 01/01/2021
B F 4000 01/01/2021
B M 6000 01/01/2021
C F 10000 01/01/2021
C F 9000 01/01/2021
C M 9000 01/01/2021
C M 9000 01/01/2021
A F 9000 01/01/2021
A F 5000 01/01/2021
A M 1000 01/01/2021
A M 1000 01/01/2021
A M 2000 01/01/2021
A F 5800 01/02/2021
A F 8100 01/02/2021
A M 3800 01/02/2021
A M 6800 01/02/2021
A M 5100 01/02/2021
B F 5800 01/02/2021
B F 3400 01/02/2021
B M 9600 01/02/2021
C F 7400 01/02/2021
C F 6500 01/02/2021
C M 4700 01/02/2021
C M 4200 01/02/2021
A F 5400 01/02/2021
A F 3700 01/02/2021
A M 6100 01/02/2021
A M 5000 01/02/2021
A M 3900 01/02/2021
A F 8700 01/03/2021
A F 7400 01/03/2021
A M 6100 01/03/2021
A M 9700 01/03/2021
A M 8800 01/03/2021
B F 3400 01/03/2021
B F 9900 01/03/2021
B M 8900 01/03/2021
C F 4400 01/03/2021
C F 5800 01/03/2021
C M 6100 01/03/2021
C M 3000 01/03/2021
A F 6600 01/03/2021
A F 9100 01/03/2021
A M 6100 01/03/2021
A M 8400 01/03/2021
A M 8500 01/03/2021

[BeaBF]

@n00ne 

MEDIAN =
VAR MEDIAN_F = CALCULATE(MEDIAN(Tabella[Salary]), Tabella[Gender] = "F")
VAR MEDIAN_M = CALCULATE(MEDIAN(Tabella[Salary]), Tabella[Gender] = "M")
RETURN MEDIAN_F / MEDIAN_M

BF