Conditional sum calculation based on two filters

[xliu1]

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

[AllisonKennedy]

@xliu1
What exactly is the desired result you want? Can you draw it out and attach the photo please?

You are getting close with the help you've been provided, but it will help you to have an understanding of what the DAX actually means, why you need to use SELECTEDVALUE, etc.

The way the SELECTEDVALUE function works, take for example:
SelectedValueDemo = SELECTEDVALUE(COURSE_FTE_SCH[Term], "Please select a term")

First, look in the column you provide as the first argument.
In our example above, this is [Term] column. If there is only 1 value selected (ie Spring) in that column, then that value is returned. If there is more than 1 value (ie Spring, Fall, Winter, Summer as you would get if you don't build the matrix to use term and don't have any filter/slicer for term) then the second argument will be returned.
In our example above, the second argument provides instructions to the report viewer to select a Term from the slicer (you would need to add this slicer as the report builder). In the DAX examples you have been given, there is no second argument, so blank is returned.
@Ibenlin 's solution should work better as a calculated COLUMN, because in columns we have the row context of the COURSE_FTE_SCH and know which [Term] value to use. In a measure, we can use SUMX(COURSE_FTE_SCH, COURSE_FTE_SCH[Fundable_SCH]) to add that row context to determine which row to check for the Term and Course Levels.

Therefore we can update the solution provided by @Ibendlin to:

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

[xliu1]

Hi @AllisonKennedy thanks for your explanation. This does help me understand how the formula works. Your formula works. but when I use the matrix visualization with Modality on Rows, TERMID on Columns, and FTE on Values I have the blank results as shown below. I do have the Term filter on this page. 

What I want to achieve is some matrix table like this. 201508/201608... are the TERMIDs grouped under the column "Term" which includes Spring, Fall, Summer.

[AllisonKennedy]

@xliu1
Can you please paste sample data table here or upload sample pbix file to onedrive so we can see what all your columns are with sample data so we know data types and constraints you need to work with?
You need to have only 1 value for Course Level as well, and I'm not sure that the TermID and Modality will provide this for you.

[xliu1]

Thanks @AllisonKennedy ! I followed your advice and put Course_Level on Rows, TERMID on Columns, FTE on Values. I am able to get a matrix visualization like this

What I want to do is a matrix visualization like the following: with Modality on Rows, TERMID on Columns, FTE on Values. I have Term, Campus, College etc. on the filters so the FTE changes dynamically when I click across different filters. How should I modify the formula?

Here is a screenshot of my dataset. Thank you!

[v-jayw-msft]

Hi @xliu1 ,

Create slicers for Term, Campus, College etc instead of using filters.

It's better to share you sample data as tables instead of pictures.

Best Regards,

Jay

[xliu1]

Thanks for your suggestiom. I do not see an option to upload file here. And my work place does not allow file sharing using one drive or Dropbox etc. Are there any other options available for new members to share file? Thanks. 

[pranit828]

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

[xliu1]

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

[pranit828]

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

[xliu1]

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

[pranit828]

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

[xliu1]

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

[pranit828]

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

[xliu1]

Hi @pranit828 thank you very much for helping me troubleshooting this. Your formula syntax works fine this time. However, when I drag it to the visualization, it does not work the way I expected.

I want to do a calculation of FTE by term and modality. I have an example matrix table "FTE by Instruction Modality." I am not sure how I can attach pbix file to this blog.

[pranit828]

Hi @xliu1 

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[modality]))

In the formula I have changed the ALLEXCEPT function section as above to calculate by modality instead. 

ALLEXCEPT(COURSE_FTE_SCH,COURSE_FTE_SCH[Term],COURSE_FTE_SCH[modality])

For some new community members, they don't have the permission to upload .pbix files. You may need to share file links via DropBox, One Drive, Drive or any other tool.

[xliu1]

Thanks for your help. 👍

[pranit828]

Hi @xliu1 

Did my solution work?

[xliu1]

Hi @pranit828 , no. I am still figuring why your formula works but when I put it to Values it returned blank results.

I have a matrix visualization with TERMID on Rows, Modality on Columns and FTE on Values. I have Term as a filer on this page. 201508/201608... are TERMIDs grouped under Term which includes Spring, Summer, Fall. what I like to achieve is something like this matrix table

[lbendlin]

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)

[xliu1]

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