Need Help - Divide based on multiple criteria

[geetika3110]

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

[v-yanjiang-msft · Accepted 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.

[tackytechtom]

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.tackytech.blog

https://www.instagram.com/tackytechtom

[tackytechtom]

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.tackytech.blog

https://www.instagram.com/tackytechtom

[geetika3110]

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"}