Forum Discussion
Need Help - Divide based on multiple criteria
Hi All,
Hope you are well.
I am new to power BI and trying to create a report and need your support with DAX measure.
Below is a sample table where i have MWD spent by month. I am looking for a formula to DIVIDE(SUM(MWD) by 19.125 if month is Jan/april/july/october i.e. 1st month of every quarter and remaining month to be multipled by 15.3.
Trying to calculate the FTE spent per month. 19.125 & 15.3 are planned Man working days based on month.
Thank you so much in advance for your support.
| Month | MWD |
| Jan | 10 |
| Jan | 20 |
| Jan | 25 |
| Jan | 33 |
| Jan | 41 |
| Jan | 48 |
| Feb | 56 |
| Feb | 63 |
| Feb | 71 |
| Feb | 78 |
| Feb | 86 |
| Feb | 93 |
| Feb | 101 |
| March | 108 |
| March | 116 |
| March | 123 |
| March | 131 |
| March | 138 |
| March | 146 |
| March | 153 |
| March | 161 |
Hi geetika3110 ,
According to your description, here's my solution.
To be divided by = IF ( MAX ( 'FTE'[Month] ) IN { "Jan", "April", "July", "October" }, 19.125, 15.3 )FTE = DIVIDE ( SUM ( 'FTE'[MWD] ), 'FTE'[To be divided by] )Get the result.
I attach my sample below for reference.
Best Regards,
Community Support Team _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Hi geetika3110 ,
According to your description, here's my solution.
To be divided by = IF ( MAX ( 'FTE'[Month] ) IN { "Jan", "April", "July", "October" }, 19.125, 15.3 )FTE = DIVIDE ( SUM ( 'FTE'[MWD] ), 'FTE'[To be divided by] )Get the result.
I attach my sample below for reference.
Best Regards,
Community Support Team _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi geetika3110 ,
I have a solution:
This is the measure I used:
TomsFTEMeasure = CALCULATE ( DIVIDE ( SUM ( Table18[MWD] ), IF ( CONTAINSROW ( {"Jan", "Apr", "July", "October"}, MAX ( Table18[Month] ) ), 19.125, 15.3 ) ) )Does this solve your issue? 🙂
/Tom
Thank you so much for your revert,
When i try to count rows using above formula, it is asking for a table and i am not able to select the column that contains "period" or "Month"
( {"Jan", "Apr", "July", "October"}
Hi geetika3110 ,
this code
CONTAINSROW ( {"Jan", "Apr", "July", "October"}, MAX ( Table18[Month] )evaluates whether the Month is either of the ones you have specified in your question. The curly brackets {"Jan", "Apr", "July", "October"} indicate a table.
/Tom
