Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Oros
Resolver III
Resolver III

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.

 

sample.jpg

10 REPLIES 10
MFelix
Super User
Super User

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))

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



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.
Oros_0-1667621446360.png

 

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.

 

Oros_1-1667621918343.png

 

Selection scenario C: 

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

Oros_2-1667622292925.png

 

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.


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



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

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

 

 

Hi,

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

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

Oros_7-1668055951241.png

 

 

Hi,

Share the download link of your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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:

 

Oros_0-1667782193903.png

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.