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.
Hi,
I have a table where I have the below info:
Date | Employee | Employment type | FTE hours |
1.1.2019 | 1 | Hourly | 160 |
1.1.2019 | 2 | Fixed | 160 |
1.1.2019 | 3 | Fixed | 160 |
- I would like to have DAX where it SUMs [FTE hours] for [Employment type] = "Fixed" and uses Measure X for [Employment type] ="Hourly"
I have tried IF CONTAINS but I guess it checks and decides TRUE or FALSE for the whole column and I would like the TRUE/FALSE test on each row.
Solved! Go to Solution.
hi, @FatherTheWizard
This looks like a measure totals problem. Very common. See this post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
and for your case, you try this formula based on the _measure
Result = var _table=SUMMARIZE(Table1,Table1[Employee],Table1[Employment type],"_total",[_measure]) return SUMX(_table,[_total])
Result:
Best Regards,
Lin
You can create a measure like:
_measure = if(selectedvalue(Table1[Employment Type])="Fixed", calculate(sum(Table1[FTE hours]), ALLSELECTED(Table1)), [Measure X])
Thanks @yelsherif , I got closer.
I think we cannot use ALLSELECTED here because the monthly figures are now shown in a wrong way between different months?
Without ALLSELECTED part of the formula the results were close but I noticed that when I individually filteres Hourly workers I got the correct answer for those employees but when I filtered both Hourly and Fixed workers it calculated according to Fixed workers way and the total was not correct (=the hourly worker part of the formula was not applied anymore).
If I elaborate the use case a bit.
As slicer filters on the report page I am using for example time (months) and also employment type (hourly, fixed). This way users can observe results based on time and based on employment types. Your current suggestion unfortunately did not work through time and when selecting only one of the employment types, I got different results compared to when selecting both of them at the same time (separate sums were not the sum I got when selecting both from slicer).
hi, @FatherTheWizard
This looks like a measure totals problem. Very common. See this post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
and for your case, you try this formula based on the _measure
Result = var _table=SUMMARIZE(Table1,Table1[Employee],Table1[Employment type],"_total",[_measure]) return SUMX(_table,[_total])
Result:
Best Regards,
Lin
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 |
---|---|
106 | |
105 | |
88 | |
75 | |
66 |
User | Count |
---|---|
126 | |
111 | |
100 | |
83 | |
71 |