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