topic Re: Need Help - Divide based on multiple criteria in DAX Commands and Tips

Need Help - Divide based on multiple criteria

geetika3110 — Fri, 11 Feb 2022 19:33:44 GMT

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

Re: Need Help - Divide based on multiple criteria

tackytechtom — Fri, 11 Feb 2022 20:32:36 GMT

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

Re: Need Help - Divide based on multiple criteria

geetika3110 — Sat, 12 Feb 2022 08:50:35 GMT

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

Re: Need Help - Divide based on multiple criteria

tackytechtom — Sat, 12 Feb 2022 09:03:23 GMT

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

Re: Need Help - Divide based on multiple criteria

v-yanjiang-msft — Tue, 15 Feb 2022 07:18:50 GMT

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.