Filter based in a table results

[Abevann]

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

[Abevann]

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

[Anonymous]

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. 

[Abevann]

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

RETURN

IF ( HASONEVALUE ( Fecha[Año] ), _Sales, _Total )

Thanks Rico, 

Abevann

[Anonymous]

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

[Abevann]

Hey Rico,

Looks great, I'll work on it and let you know the results.

Thanks a lot, regards,

Abevann

[Anonymous]

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

[mohammedadnant]

@Abevann 

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

www.youtube.com/taik18

[Abevann]

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

[Anonymous]

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.