Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
How can I structure a DAX query to accomplish this?
Solved! Go to 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.
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!
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
@v-shex-msft Here's the pbix file: https://drive.google.com/file/d/0BzgZ70kTLVb-X0RIMjlIM3VfbU0/view?usp=sharing
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |