Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
gattoun
Frequent Visitor

DAX Query - Display Difference Between Two Datasets

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? 

1 ACCEPTED SOLUTION
alena2k
Resolver IV
Resolver IV

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.

View solution in original post

4 REPLIES 4
alena2k
Resolver IV
Resolver IV

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.

This is perfect. Thank you so much!

v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.