topic Re: Problem with DAX formula (average of divided medians caluclated by conditions) in DAX Commands and Tips

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

n00ne — Wed, 04 May 2022 09:13:32 GMT

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.

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

BeaBF — Wed, 04 May 2022 09:53:32 GMT

n00ne Hi!

First of all i notice an error, the average for Position A and Gender M should be 8000, not 7000. In fact, 10000 + 7000 + 7000 = 24000 / 3 = 8000.

To achieve the first expected output, you can calculate this measure:

AVG_SALARY = CALCULATE(AVERAGE(Tabella[Salary]), Tabella[Gender] = "F") / CALCULATE(AVERAGE(Tabella[Salary]), Tabella[Gender] = "M")

by adding it to your matrix, you obtain:

Here the construction:

where Average of salary is the average of the native field Salary, selected in this way:

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

n00ne — Wed, 04 May 2022 10:05:13 GMT

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

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

BeaBF — Wed, 04 May 2022 10:11:59 GMT

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

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

n00ne — Wed, 04 May 2022 10:24:35 GMT

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.

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

BeaBF — Wed, 04 May 2022 10:40:42 GMT

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

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

n00ne — Wed, 04 May 2022 11:45:56 GMT

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?

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

BeaBF — Wed, 04 May 2022 12:31:51 GMT

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.

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

n00ne — Wed, 04 May 2022 12:33:59 GMT

Hi BeaBF ,

Any idea how I can get the needed result?

The result visualisation is not a table but line graph.

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

BeaBF — Wed, 04 May 2022 12:42:24 GMT

n00ne paste me a sample plis.

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

n00ne — Wed, 04 May 2022 12:55:20 GMT

BeaBF ,

Here you go:

Line chart will be the end result of this task.

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

BeaBF — Wed, 04 May 2022 15:04:01 GMT

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,

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

n00ne — Wed, 04 May 2022 15:29:43 GMT

Hi BeaBF ,

Please find under link excel file:

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

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

BeaBF — Wed, 04 May 2022 15:33:26 GMT

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

paste here the date column plis 🙂

THX,

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

n00ne — Wed, 04 May 2022 15:42:22 GMT

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

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

BeaBF — Wed, 04 May 2022 15:51:27 GMT

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

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

n00ne — Wed, 04 May 2022 16:02:25 GMT

BeaBF

It's not quite the results I am expecting.

january should be 1.19

February 1.05

March 0.94

(as per calculations screen I have shared above)

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

tamerj1 — Thu, 05 May 2022 04:01:57 GMT

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 )

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

n00ne — Thu, 05 May 2022 07:05:31 GMT

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?

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

tamerj1 — Thu, 05 May 2022 07:18:40 GMT

n00ne
Can you clarify further perhaps using an example?