Forum Discussion

Smritide's avatar
Smritide
Regular Visitor
9 years ago
Solved

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 ...
Need Help
  • Smritide's avatar
    Smritide
    9 years ago

    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.

     

     

Smritide's avatar
Smritide
Regular Visitor
9 years ago

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.

 

 

v-huizhn-msft's avatar
v-huizhn-msft
Microsoft Employee
9 years ago

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