Forum Discussion

ClearRide's avatar
ClearRide
Frequent Visitor
3 years ago
Solved

Counting with filter based on two tables

I need to count the number of People who have met their sales quotas.
This is the Dax I have right now:

Qualify Count = CALCULATE(COUNT(Org[SalesID]),  FILTER(Org, Sum(Sales[Sales] -  Sum(Org[Quota]) > 0))
 
I want to count the number of Sales Persons who's total sales is higher than their quota.
There is an one to many relationship between Org and Sales. 
This measure isn't working and I think it's because the filter is based on two tables.
Need Help
  • v-zhangti's avatar
    v-zhangti
    3 years ago

    Hi, ClearRide 

     

    After testing, it should be related to the context of the measure calculation. You can try the following methods.

    Measure1 = SUM(Sales[Sales])
    Measure2 = SUM(Org[Quota])
    Qualify Count = 
 CALCULATE(COUNT(Org[Sales ID]),FILTER(ALL(Org), [Measure1]>[Measure2]))

    At this point, the correct result is calculated.

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi, ClearRide 

     

    After testing, it should be related to the context of the measure calculation. You can try the following methods.

    Measure1 = SUM(Sales[Sales])
    Measure2 = SUM(Org[Quota])
    Qualify Count = 
 CALCULATE(COUNT(Org[Sales ID]),FILTER(ALL(Org), [Measure1]>[Measure2]))

    At this point, the correct result is calculated.

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.