Forum Discussion

Abevann's avatar
Abevann
Icon for Helper III rankHelper III
5 years ago

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...
Need Help
mohammedadnant's avatar
mohammedadnant
Icon for Impactful Individual rankImpactful Individual
5 years ago

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's avatar
    Abevann
    Icon for Helper III rankHelper III
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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.