DISTINCT MEASURE

[Oros]

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.

[MFelix]

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]

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:

[MFelix]

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

[Oros]

Hi @MFelix  and @Ashish_Mathur,

There are only 2 tables involved.  Here is the mock up.  Thank you so much for your kind help.

https://docs.google.com/spreadsheets/d/1b-JeseNitWMYtcAf2F9sLLfxJ1aUI11T/edit?usp=sharing&ouid=10436...

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Oros]

Hi @Ashish_Mathur,

Thank you for the pbi file.  I noticed that there is no measure for the 'Customers Who Bought'

Also, the table that can be created from the pbi file still cannot achieve the scenarios as outlined BASED on number of customers.

Assuming the total number of active customers (active means who bought) = 67

Selection A:

If the dropdown filters for Product and Salesperson is default as ALL, then the 4 table columns must be (result):

ASSIGNED TO SALESPERSON = 67

CUSTOMERS WHO BOUGHT = 67

OVERALL ACTIVE CUSTOMERS = 67

OVERALL CUSTOMERS WHO BOUGHT = 67

Selection B:

A product is selected and only 6 customers bought this product.  These 6 customers are all assigned to Salesperson #10 who has 10 assigned customers. The resulting table must be:

ASSIGNED TO SALESPERSON = 10

CUSTOMERS WHO BOUGHT = 6

OVERALL ACTIVE CUSTOMERS = 67

OVERALL CUSTOMERS WHO BOUGHT = 6

The ultimate goal is basically to get the ratio between

1. How many CUSTOMERS WHO BOUGHT vs how many CUSTOMERS ASSIGNED TO A

SALESPERSON

2.  How many OVERALL CUSTOMERS WHO BOUGHT vs how many TOTAL ACTIVE CUSTOMERS

Thanks agian.

[Ashish_Mathur]

Hi,

I just cannot understand your requirement.  See if this revised file helps.

[Oros]

Hello @Ashish_Mathur ,

I think that the confusion was on the 'ASSIGNED TO SALESPERSONS'.  This should mean how many customers are assigned to each or all salesperons (NOT who is the salesperson assigned).

Here is the context of the report that requires 4 columns.  Thank you for your patience and help.

[Ashish_Mathur]

Hi,

Share the download link of your PBI file.

[Oros]

Hi @Ashish_Mathur ,

Thank you again for your reply and sorry for the confusion.  I basically need 4 measures (will be as 4 columns) and must follow based on the context (selection).  Here is the summary: