DAX Query - Display Difference Between Two Datasets

[gattoun]

I'm building a tornado chart that displays average salary differences by gender and pay grade. For each pay grade, I'd like to have ONE bar on the left (female) or right (male) showing the difference in average pay for that grade. If only one gender is present in that grade, then there shouldn't be a bar. The logic should be something like "if two genders are present in this pay grade, then take average of each gender and subtract smaller from larger, and add a flag stating which gender is larger." So I'm thinking I need both a new measure (the difference) and a new column (the gender flag). Attempted DAX query and chart output:

https://ibb.co/bBE60m

https://ibb.co/eVJYfm

How can I structure a DAX query to accomplish this? 

[alena2k · Accepted solution]

I have alternative visual solution. Try and see if you like it.  At least it should take you half way there.

Create 2 measures and display both of them as values it on your tornado control:

Advantage F = 
VAR
  avgF = CALCULATE(AVERAGE(tbl[salary]), tbl[Gender] ="F")
VAR
  avgM = CALCULATE(AVERAGE(tbl[salary]), tbl[Gender] ="M")
VAR
  gap   = avgM - avgF
RETURN
  IF(	gap > 0 || avgF = 0 || avgM = 0, 0, abs(gap))

Advantage M = 
VAR
  avgF = CALCULATE(AVERAGE(tbl[salary]), tbl[Gender] ="F")
VAR
  avgM = CALCULATE(AVERAGE(tbl[salary]), tbl[Gender] ="M")
VAR
  gap   = avgM - avgF
RETURN
  IF(	gap <= 0 || avgF = 0 || avgM = 0, 0, gap)

As a result you will have presence of the bar as indicator on which gender has advantage. I hope it will help.

[gattoun]

This is perfect. Thank you so much!

[Anonymous]

Hi @gattoun,

If you can, please share the pbix file for further testing. It is hard to clarify your scenario from screenshots.

BTW, please do mask sensitive data if your contents contains any privacy data.

Regards,

Xiaoxin Sheng

[gattoun]

@Anonymous Here's the pbix file: https://drive.google.com/file/d/0BzgZ70kTLVb-X0RIMjlIM3VfbU0/view?usp=sharing