Forum Discussion

Oros's avatar
Oros
Post Prodigy
3 years ago

DISTINCT MEASURE

I have the following measures that separately shows in a card as follow.  The TOTAL ASSIGNED CUSTOMERS (EXCLUDING NO SALES) keeps on changing to the same value as the DISTINCT CUSTOMERS COLUMN.  There must be something wrong with my measures.

 

DISTINCT CUSTOMERS (BOUGHT SELECTED ITEMS) = CALCULATE(DISTINCTCOUNT(SALES TABLE[CUSTOMERS]),FILTER('SALES TABLE',[Sales]>0))

 

TOTAL ASSIGNED CUSTOMERS (TO A SALESPERSON) = DISTINCTCOUNT(Customer_Card[No]) 

 

TOTAL ASSIGNED CUSTOMERS (EXCLUDING NO SALES)  = CALCULATE(DISTINCTCOUNT(Customer[No]),FILTER('SALES TABLE',[Sales]>0))

 

When an item is selected, the third column value should NOT change.

 

Need Help

10 Replies

  • MFelix's avatar
    MFelix
    Super User
    3 years ago

    Hi Oros ,

     

    This is related with the way you are filtering the measure, since you are doing a FILTER(SALES TABLE, ,[Sales]>0) this is returning the filter for the current selection and you are not overcoming the filter context in this case try the following code:

    TOTAL ASSIGNED CUSTOMERS (EXCLUDING NO SALES)  = CALCULATE(DISTINCTCOUNT(Customer[No]),FILTER(ALL('SALES TABLE'),[Sales]>0))
    • Oros's avatar
      Oros
      Post Prodigy
      3 years ago

      Hi MFelix ,

       

      Thank you very much for your reply.

       

      I think that your solution is very, very close to solve this problem.  Maybe there is something that I am still doing wrong.  Thank you again for your kind help!

       

      I have four measures:

      ASSIGNED TO SALESPERSON = CALCULATE(DISTINCTCOUNT(Customer_Card[No]),FILTER('SALES TABLE',[Sales]>0))
      CUSTOMERS WHO BOUGHT = CALCULATE(DISTINCTCOUNT(SALES TABLE[CUSTOMERS]),FILTER('SALES TABLE',[Sales]>0))
      OVERALL ACTIVE CUSTOMERS = CALCULATE(DISTINCTCOUNT(Customer_Card[No]),FILTER('SALES TABLE',[Sales]>0))
      OVERALL CUSTOMER WHO BOUGHT = CALCULATE(DISTINCTCOUNT(SALES TABLE[CUSTOMERS]),FILTER('SALES TABLE',[Sales]>0))
       
      No matter what the selection is, OVERALL ACTIVE CUSTOMERS column must always stay at 601.
      (numbers with check mark or no mark are correct)
       
      Selection scenario A:
      If NO filter is selected (ITEM or SALESPERSON), the numbers are all correct at 601.

       

      Selection scenario B:

      IF an item AND a salesperson are selected, all the numbers in the table

      change but they are all the same numbers. The 3 cards stay correct.

      In the table, only CUSTOMER WHO BOUGHT column is correct for the selection made.

       

       

      Selection scenario C: 

      IF only all items is selected and a salesperson here is the problem: