Show two seperatly filtered visuals on same table

[Anonymous]

Hi all,

I have the following snowflake datamodell with the following tables:

• All Sales, which containes all sales data
• Sales Person contains information about the sales staff
• Based on if the sales person is internal or external, information about the business unit or the external businessis stored in the Business Unit or External Business Table

My table has one slicer, where I can select a single sales person and I am trying to show the following information in this table:

•  Information about the selected sales person: name, name of business unit and total sales
•  Based on the business unit the sales person is in, I would look to show information about the business unit, such as all sales staff in the business unit, name of the business unit and total sales for each sales person.

Visually I am trying to achieve the following:

However, since my slicer already limits the data in the All Sales table to sales data of the Sales Person Alois, I decided to use measures to calculate the Ranking Sales Person in Business Unit visual (I am open to other suggestions, if this is not the right approach).

To calculate the total sales, I use the following measure:

Total Sales =
CALCULATE (
    SUM ( 'AllSales'[Sales] ),
    ALL ( 'AllSales'),
    FILTER (
        'BusinessUnit',
        'BusinessUnit'[Name] = SELECTEDVALUE('BusinessUnit'[Name])
    )
)

Now I am stuck calculating the corresponding name of each sales person that relates to the sales.

Any suggestion on how I can achieve this using a measure?

[Icey · Accepted solution]

Hi @Anonymous ,

It is suggested to create another independent table for your slicer, like:

SalesPersonSlicer = VALUES('All Sales'[Sales Person])

Then, use it as a slicer. And create measures like so:

Measure 1 =
IF (
    MAX ( 'All Sales'[Sales Person] )
        = SELECTEDVALUE ( 'SalesPersonSlicer'[Sales Person] ),
    1
)

Measure 2 =
VAR SelectedBusinessUnit =
    CALCULATE (
        MAX ( 'All Sales'[Business Unit] ),
        FILTER ( 'All Sales', [Measure] = 1 )
    )
RETURN
    IF ( MAX ( 'All Sales'[Business Unit] ) = SelectedBusinessUnit, 1 )

Then, put [Measure 1] in table visual 'Sales Person Overview' and set as 1. And put [Measure 2] in another table visual and set as 1.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

This definitly helped me to get back on track. Thanks @Icey