cancel
Showing results for
Did you mean:
Highlighted Helper II

## Conditional sum calculation based on two filters

I try to create a new measure which does the sum based on two conditions: COURSE_FTE_SCH is the table name, [Term],[Course_Level] and [Fundable_SCH ]are the columns within this table. How could I combine these conditions into one dax formula? Thanks!

If COURSE_FTE_SCH[Term] in {"Spring","Fall"} and COURSE_FTE_SCH[Course_Level] in {"Lower UG","Upper UG"} Then

FTE = CALCULATE(SUM(COURSE_FTE_SCH[Fundable_SCH])/15)*0.375

If COURSE_FTE_SCH[Term] in {"Spring","Fall"} and COURSE_FTE_SCH[Course_Level] in {"Graduate"} Then

FTE = CALCULATE(SUM(COURSE_FTE_SCH[Fundable_SCH])/12)*0.375

If COURSE_FTE_SCH[Term] in {"Summer"} and COURSE_FTE_SCH[Course_Level] in {"Lower UG","Upper UG"} Then

FTE = CALCULATE(SUM(COURSE_FTE_SCH[Fundable_SCH])/10)*0.25

If COURSE_FTE_SCH[Term] in {"Summer"} and COURSE_FTE_SCH[Course_Level] in {"Graduate"} Then

FTE = CALCULATE(SUM(COURSE_FTE_SCH[Fundable_SCH])/8)*0.25

20 REPLIES 20
Highlighted Super User V

## Re: Conditional sum calculation based on two filters

something like this. Note that you are missing the "or else" option, and using "in" with single values is a bit rich.

``````FTE = SWITCH(TRUE(), COURSE_FTE_SCH[Term] in {"Spring","Fall"} && COURSE_FTE_SCH[Course_Level] in {"Lower UG","Upper UG"},CALCULATE(SUM(COURSE_FTE_SCH[Fundable_SCH])/15)*0.375,
COURSE_FTE_SCH[Term] in {"Spring","Fall"} && COURSE_FTE_SCH[Course_Level] in {"Graduate"},
CALCULATE(SUM(COURSE_FTE_SCH[Fundable_SCH])/12)*0.375,
COURSE_FTE_SCH[Term] in {"Summer"} && COURSE_FTE_SCH[Course_Level] in {"Lower UG","Upper UG"},
CALCULATE(SUM(COURSE_FTE_SCH[Fundable_SCH])/10)*0.25,
COURSE_FTE_SCH[Term] in {"Summer"} && COURSE_FTE_SCH[Course_Level] in {"Graduate"},
CALCULATE(SUM(COURSE_FTE_SCH[Fundable_SCH])/8)*0.25 )``````

I also hope the numbers you provided were just samples.  In this sample scenario the semester doesn't matter at all. You could have rewritten the measure as

``FTE = SUM(COURSE_FTE_SCH[Fundable_SCH]) * if(COURSE_FTE_SCH[Course_Level] ="Graduate",0.03125,0.025)``

Highlighted Community Champion

## Re: Conditional sum calculation based on two filters

HI @xliu1

I would use the below formula and plot it in the matrix chart with rows as Term and Course level.

``FTE = CALCULATE(SUM(COURSE_FTE_SCH[Fundable_SCH]) * if(COURSE_FTE_SCH[Course_Level] ="Graduate",0.03125,0.025), ALLEXCEPT(COURSE_FTE_SCH,COURSE_FTE_SCH[Term],COURSE_FTE_SCH[Course_Level]))``

Did I resolve your issue? Mark my post as a solution!

Appreciate your Kudos, Press the thumbs up button!!

Regards,
Pranit

Hope it resolves your issue? Please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos, Press the thumbs up button!!👍

Regards,
Pranit

Highlighted Helper II

## Re: Conditional sum calculation based on two filters

Hi @pranit828 , thanks for your prompt reply. I tried your formula and I got this error message. Highlighted Helper II

## Re: Conditional sum calculation based on two filters

Hi @lbendlin  thanks for your reply. I tried your formula and got this error message: Any idea what is wrong? Thanks Highlighted Community Champion

## Re: Conditional sum calculation based on two filters

Hi @xliu1

``FTE = CALCULATE(SUM(SELECTEDVALUE(COURSE_FTE_SCH[Fundable_SCH])) * if(SELECTEDVALUE(COURSE_FTE_SCH[Course_Level]) ="Graduate",0.03125,0.025), ALLEXCEPT(COURSE_FTE_SCH,COURSE_FTE_SCH[Term],COURSE_FTE_SCH[Course_Level]))``

IF you get any more error add the SELECTEDVALUE function as I have added for the error above.

Did I resolve your issue? Mark my post as a solution!

Appreciate your Kudos, Press the thumbs up button!!

Regards,
Pranit

Hope it resolves your issue? Please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos, Press the thumbs up button!!👍

Regards,
Pranit

Highlighted Helper II

## Re: Conditional sum calculation based on two filters

Hi @pranit828 I copy/paste your formula but still get this error message Highlighted Community Champion

## Re: Conditional sum calculation based on two filters

Hi @xliu1

I would modify it to

``FTE = CALCULATE(SUMX(SELECTEDVALUE(COURSE_FTE_SCH[Fundable_SCH])) * if(SELECTEDVALUE(COURSE_FTE_SCH[Course_Level]) ="Graduate",0.03125,0.025), ALLEXCEPT(COURSE_FTE_SCH,COURSE_FTE_SCH[Term],COURSE_FTE_SCH[Course_Level]))``

Let me know if you still have an error.

Hope this resolves your issue? Mark my post as a solution!

Regards,
Pranit

Hope it resolves your issue? Please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos, Press the thumbs up button!!👍

Regards,
Pranit

Highlighted Helper II

## Re: Conditional sum calculation based on two filters

Thanks @pranit828 , I tried SUMX function as you did but this is the error message Highlighted Community Champion

## Re: Conditional sum calculation based on two filters

Hi @xliu1

I would modify it to

``FTE = CALCULATE(SUMX(COURSE_FTE_SCH,SELECTEDVALUE(COURSE_FTE_SCH[Fundable_SCH])) * if(SELECTEDVALUE(COURSE_FTE_SCH[Course_Level]) ="Graduate",0.03125,0.025), ALLEXCEPT(COURSE_FTE_SCH,COURSE_FTE_SCH[Term],COURSE_FTE_SCH[Course_Level]))``

OR

``FTE = CALCULATE(SUMX(COURSE_FTE_SCH,COURSE_FTE_SCH[Fundable_SCH]) * if(COURSE_FTE_SCH[Course_Level] ="Graduate",0.03125,0.025), ALLEXCEPT(COURSE_FTE_SCH,COURSE_FTE_SCH[Term],COURSE_FTE_SCH[Course_Level]))``

If this doesn't work, I would try once with removing the SELECTEDVALUE function one by one.

May be a better idea to reply back with the .PBIX file if further debugging required.

Hope this resolves your issue? Mark my post as a solution!

Regards,
Pranit

Hope it resolves your issue? Please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos, Press the thumbs up button!!👍

Regards,
Pranit

Announcements #### Power Platform Community Conference

Check out the on demand sessions that are available now! #### Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event. #### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021 #### Get Ready for Power BI Dev Camp 