Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 |
Solved! Go to Solution.
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 _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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
https://www.instagram.com/tackytechtom
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! | |
#proudtobeasuperuser | |
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
https://www.instagram.com/tackytechtom
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! | |
#proudtobeasuperuser | |
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"}
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
27 | |
13 | |
11 | |
9 | |
6 |