Forum Discussion
Filter based in a table results
Guys, I have a requirement. I have a DB with salespersons that have been taking care of several customers. Each customer has been buying for some years and sometimes they change the salesperson. They want to see a comparative graph for the same customer independently of who the salesperson is. For instance:
Month Year Salesperson Customer Sales
October 2020 Jerry John’s Hardware store 3,000.00
September 2020 Jerry Bed, Bath & Beyond 14,000.00
March 2020 John Bed, Bath & Beyond 20,000.00
November 2019 John Bed, Bath & Beyond 32,000.00
They would like to filter the sales of a specific salesperson, and compare with previous sales, independently the person in charge of the account to understand the grow of the account. Then the result should look like:
2020 Jerry 37,000.00
2019 Jerry 32,000.00
Even that he was not the salesperson in 2019. So far, I have the table with the filtered salesperson and with the assigned accounts (customers).
Month Year Salesperson Customer Sales
October 2020 Jerry John’s Hardware store 3,000.00
September 2020 Jerry Bed, Bath & Beyond 14,000.00
Now, I would like to filter the sales for all the accounts assigned to Jerry in the example, even if he were not the person in charge of it in 2019, like this:
Month Year Customer Sales
October 2020 John’s Hardware store 3,000.00
September 2020 Bed, Bath & Beyond 14,000.00
March 2020 Bed, Bath & Beyond 20,000.00
November 2019 Bed, Bath & Beyond 32,000.00
Any suggestion of how to do this? Thanks a lot, regards
Abevann
9 Replies
Good day,
I think you can add JERRY in the customer table as sales person or whatever the customer he deals, add his name in customer table as sales person.
Then, when you use Jerry from customer table and sales value from sales table with year, you will get the result as desired.
I hope this will help you..
Thanks & Regards,
Mohammed Adnan
Hello Mohammed,
Thanks for your reply. There is only one table with all this data, and the problem is that I have to filter first the specific sales man, read all the customers for him, fiter all the previous information for this customers and unfilter salesperson, because there were sales for this customers and was not the same salesperson. They want to compare the sales with previous years, to understand if the new salesperson is performing better than the previous one with the same customer.
At the end, there should be a graph with sales of all related customers only who ever the salesperson is.
Hope this clarifies, thanks...
Abevann
Hi Abevann
I achieve your goal by building a salesperson slicer table and measures.
Salesperson Table:
Salesperson = VALUES('Table'[Salesperson])Measures:
M.Salesperson = SELECTEDVALUE(Salesperson[Salesperson])M.Sales = VAR _CustomerTable = SUMMARIZE ( FILTER ( ALL ( 'Table' ), 'Table'[Salesperson] = [M.Salesperson] ), 'Table'[Customer] ) VAR _Sales = SUMX ( FILTER ( 'Table', 'Table'[Year] = MAX ( 'Table'[Year] ) && 'Table'[Customer] IN _CustomerTable ), 'Table'[Sales] ) VAR _Total = SUMX ( FILTER ( 'Table', 'Table'[Customer] IN _CustomerTable ), 'Table'[Sales] ) RETURN IF ( HASONEVALUE ( 'Table'[Year] ), _Sales, _Total )Show Items When Value is 1 = VAR _CustomerTable = SUMMARIZE ( FILTER ( ALL ( 'Table' ), 'Table'[Salesperson] = [M.Salesperson] ), 'Table'[Customer] ) RETURN IF ( HASONEFILTER ( Salesperson[Salesperson] ), IF ( MAX ( 'Table'[Customer] ) IN _CustomerTable, 1, 0 ), 1 )Build two table visuals as below.
Table visual1:
Table visual 2 compare the selected salesperson's sales by year.
Build a slicer by salesperson column in Salesperson Table. Result is as below.
Select Jerry in Slicer.
You can download the pbix file from this link: Filter based in a table results
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hey Rico,
Looks great, I'll work on it and let you know the results.
Thanks a lot, regards,
Abevann
Hi Abevann
Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.
Best Regards,
Rico Zhou
Hi Rico
I have an issue with one value, and wondering why; it doesn't accept alphanumeric values, only numbers in OINV[CardName] (in bold):
M.Sales =VAR _CustomerTable =SUMMARIZE (FILTER ( ALL ( 'OINV' ), OINV[SlpCode]= [M.Salesperson] ),OINV[CardName])VAR _Sales =SUMX (FILTER ('Fecha',Fecha[Año] = MAX ( Fecha[Año] )&& OINV[CardName] IN _CustomerTable),OINV[Ventas menos notas de crédito])VAR _Total =SUMX ( FILTER ( 'OINV', OINV[CardName] IN _CustomerTable ), OINV[Ventas menos notas de crédito] )RETURNIF ( HASONEVALUE ( Fecha[Año] ), _Sales, _Total )Thanks Rico,AbevannHi Abevann
It seems that you have a Dimdate table (Fecha). You can try to use the date column in OINV table in _Sales.
Did you related two tables by dates?
Please show me a sample like what you are dealing with.
This may make it easier for me to understand your data model.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Sure RIco,
In this dashboard, I filtered the salesperson (Carlos Alejandro Avila), and got several customers. As you can see, in the graph, there are no sales last year in DocTotal periodo anterior and DocTotal, only sales for this 2020.
In below picture, I filtered the customer Magaña Olmos Alejandro and unfiltered the salesperson, and then, there are sales for previous year
Once I change the date filter to include 2019, the previous salesperson name (Fabian Argueta) appears in the table:So, they need to compare the sales of all customers regardless who the salesperson was, which cannot be done with the dashboard I have done so far.
Now the date is linked to a date table to avoid discontinuities on information:
Hope this clarifies the issue, thanks Rico,
Abevann
Hi Abevann
Is your issue that when filter salesperson, you can't see sales for previous years？
Your data model seems to be complex.
Could you provide me a sample ?
This may make it easier for me to understand your issue.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
