Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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.
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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:
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:
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix and @Ashish_Mathur,
There are only 2 tables involved. Here is the mock up. Thank you so much for your kind help.
Hi,
You may download my PBI file from here.
Hope this helps.
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.
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.
Hi,
Share the download link of your PBI file.
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:
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
83 | |
71 | |
49 |
User | Count |
---|---|
143 | |
120 | |
110 | |
59 | |
57 |