cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
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
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
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
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. 

 

xliu1_0-1597546101231.png

 

Highlighted
Helper II
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

xliu1_0-1597546930701.png

 

Highlighted
Community Champion
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
Helper II

Re: Conditional sum calculation based on two filters

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

xliu1_0-1597549545877.png

 

Highlighted
Community Champion
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!

 

I am trying to help you. 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
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

xliu1_0-1597550129705.png

 

Highlighted
Community Champion
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!

 

I am trying to help you. 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


Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors