Forum Discussion

xliu1's avatar
xliu1
Helper II
6 years ago

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 com...
Need Help
xliu1's avatar
xliu1
Helper II
6 years ago

Hi Anonymous 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.

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
    xliu1
    Helper II
    6 years ago

    Thanks for your help. 👍

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi xliu1 

     

    Did my solution work?

  • xliu1's avatar
    xliu1
    Helper II
    6 years ago

    Hi Anonymous , 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