Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Dax - Difference Between Medians in Quartile Groups

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 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    I have found a work around using Summarize tables

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    I have found a work around using Summarize tables

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Anonymous ,

      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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ,

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

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

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

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