Forum Discussion
Conditional sum calculation based on two filters
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 )
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.
- 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.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!
