cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Using FILTER with SUMX & DISTINCT

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. 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Regular Visitor

Re: 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.

 

 

View solution in original post

5 REPLIES 5
Highlighted
Regular Visitor

Using FILTER with SUMX and DISTINCT

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. 

Highlighted
Super User IV
Super User IV

Re: Using FILTER with SUMX & DISTINCT

I guess the first question I would have is "why"? If denied students are $0 then including them in a SUM would not affect the result.


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Regular Visitor

Re: 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.

 

 

View solution in original post

Highlighted
Super User III
Super User III

Re: Using FILTER with SUMX and DISTINCT

Hi @Smritide,

Trt to put your sumx inside a CALCULATE something like this:

CALCULATE(SUMX(DISTINCT(Master[Revenue]),Master[Revenue]), Master[Admmited] ="admitted")

Regards,
MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





Highlighted
Microsoft
Microsoft

Re: Using FILTER with SUMX & DISTINCT

Hi @Smritide,

Congratulations, you resolved the problem by yourself, please mark the solution as workaround, so that more people will benefit from here.

Best Regards,
Angelia

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Top Solution Authors
Top Kudoed Authors