Dax - Difference Between Medians in Quartile Groups

[Stuartm1983]

Hi,

I've included a sample of my data (It is missing another column call 'Quartile Group' : ('Q1','Q2','Q3','Q4')

For each Quartile Group I'm looking to produce a Matrix table with Sex as the column and Quartile Group in the Rows with Median amount for Pay. (so far so good - Easy).

The bit I can't figure out is how to produce a % based upon ((Male Median Pay - Female Median Pay) / Male Median Pay).

The closest I got was using CALCULATE in a seperate table, but does not work when split out by Sex. I get infinity and 0 when split out.

Median_Table = SUMMARIZE(Summary,Summary[Quartile Group],
Summary[Sex:People],
"Median Pay",Median(Summary[Ordinary Pay - Hourly - Final]),

"Median Male",CALCULATE(MEDIAN(Summary[Ordinary Pay - Hourly - Final]),FILTER(Summary,Summary[Sex:People]="Male")),

"Median Female",CALCULATE(MEDIAN(Summary[Ordinary Pay - Hourly - Final]),FILTER(Summary,Summary[Sex:People]="Female")),

"Pay Gap",((CALCULATE(MEDIAN(Summary[Ordinary Pay - Hourly - Final]),FILTER(Summary,Summary[Sex:People]="Male"))-(CALCULATE(MEDIAN(Summary[Ordinary Pay - Hourly - Final]),FILTER(Summary,Summary[Sex:People]="Male"))))))

Pay Gap is blank or infinity and I think it's due to context but I can not fix this.

Any help would be great.

S 

[Stuartm1983 · Accepted solution]

I have found a work around using Summarize tables

[v-yiruan-msft]

Hi @Stuartm1983 ,

Thanks fro your feedback. Could you please share your workaround and mark your post as Answered if it is convenient? It will help the others in the community find the solution quickly if they face the same problem with yours. Thank you.

Best Regards

[Stuartm1983]

We not able to upload data, I'm afraid.

[v-yiruan-msft]

Hi @Stuartm1983 ,

Please create a measure as below and check whether it can return the correct result. If no, please provide some sample data in table Summary and your expected result with specifc examples. Thank you.

Difference% =
VAR _MMedianPay =
    CALCULATE (
        MEDIAN ( Summary[Ordinary Pay - Hourly - Final] ),
        FILTER ( Summary, Summary[Sex:People] = "Male" )
    )
VAR _FMedianPay =
    CALCULATE (
        MEDIAN ( Summary[Ordinary Pay - Hourly - Final] ),
        FILTER ( Summary, Summary[Sex:People] = "Female" )
    )
RETURN
    DIVIDE ( _MMedianPay - _FMedianPay, _MMedianPay )

Best Regards

[Stuartm1983]

Hi,

Thanks for the reply.

It's returning the same issue as I faced. The total difference was correct but but the within Sex categories difference are either blank or 100%.

[v-yiruan-msft]

Hi @Stuartm1983 ,

Could you please share some sample data in table Summary and your expected result with specific examples in order to make troubleshooting and provide a suitable solution? By the way, the column Quartile Group is a default field in table or a calculated column? If it is a calculated column, could you please also provide its formula. Thank you.

Calculating Median Value Using DAX In Power BI

Calculating percentiles by group in Power BI

CALCULATING QUARTILES WITH DAX AND POWER BI

Best Regards