Forum Discussion
Using FILTER with SUMX & DISTINCT
Hey,
I just figured it out.
CALCULATE(SUMX(DISTINCT('Event Details'[Event Category (City)]),Master[Avg Cost]),FILTER(Master,Master[Decision Name]="Admitted")
The problem was that there is a cost associated against each denied item as well so the previous function returned a sum off all positives and negative returns wherein I only wanted positive returns.
Hi
I have two tables -
1) A student information table (Master Table) stating if they were admitted or denied for a course. For those admitted, I have a column calculating revenue earned from that student (for denied students it return $0). The table also indicates name of the event students attended and learned about the course.
2) An events table (Event Category) with names of each event and cost associated with the event.
I want to calculate ROI for each event. I used the following formula.
ROI = DIVIDE([Returns],SUMX(DISTINCT('Event Details'[Event Category (City)]),Master[Avg Cost]),0)
Where,
Returns = SUMX(DISTINCT(Master[Revenue]),Master[Revenue])-SUMX(DISTINCT('Event Details'[Event Category (City)]),Master[Avg Cost])
I used the sumx and distinct function as I wanted to sum each row for admitted students returning cost against specific events. e.g. if you consider the following scenario, I want sum of cost associated with Event A, Event B and Event C where Event A's cost is calculated only once.
Student A Admitted Event A
Student B Admitted Event B
Student C Denied Event A
Student D Admitted Event C
The SUMX and DISTINCT function does that for me, however I want to put a filter in the formula so that the measure calculates the returns only for Admitted students.
Can you help me figure this out? I tried using FILTER but it does not work.
