evaluation of sales selected employee vs. Compare Group

[addicted87]

hi @all,

following problem:

I have 3 products: P1, P2, P3

I have 4 employees: E1, E2, E3, E4

now I have follwing table:

product, sales, employee

product	sales	employee
P1	10	E1
P1	5	E2
P1	15	E3
P1	3	E4
P2	20	E2
P2	10	E3
P3	30	E2
P3	25	E4

What I like to do is now:

I need a table showing following result, when I select employee E1

product	sales selected employee E1	sales other employees
P1	10	23
P2	0 or blank	30
P3	0 or blank	55

But if i select E1 in dimension employee, I filter the data, so I will get just the row for P1, but I like to see the other rows as well. How can I fix that?

thanks for your help.

[v-yingjl · Accepted solution]

Hi @addicted87 ,

Based on your descriton, you can extract the employee as a single table to use it as a slicer:

Employee = DISTINCT('Table'[employee])

Create these two measures:

Selected emplyee sales =
VAR _sum =
    CALCULATE (
        SUM ( 'Table'[sales] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[employee] IN DISTINCT ( 'Employee'[employee] )
                && 'Table'[product] IN DISTINCT ( 'Table'[product] )
        )
    )
RETURN
    IF ( ISBLANK ( _sum ), 0, _sum )

Other employees sales =
CALCULATE (
    SUM ( 'Table'[sales] ),
    FILTER ( ALL ( 'Table' ), 'Table'[product] IN DISTINCT ( 'Table'[product] ) )
) - [Selected emplyee sales]

Attached a sample file in the below, hopes to help you.

Best Regards,
Yingjie Li

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

[amitchandak]

@addicted87 , Try measures like

sales selected employee= sum(Table[sales])

sales other employees = calculate([sales selected employee], all(Table)) -[sales selected employee]

I needed , try first measure with +0

sales selected employee= sum(Table[sales])+0

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.

[addicted87]

thx for your reply @amitchandak ,

your measure works well for product p1, BUT I will still get just one row (for p1) in my result table, instead of 3 (p1, p2, p3). The problem ist, that there is no dataset für p2 or p3 for the employee p1, so if i filter dimension employee, I will product dimension as well. Thats the big problem. Is there a way, not get all vales of product in the table?

[addicted87]

In Qlik Sense it looks like this. I select via filter employee 1, and get the first measure (column 2). Then I sum up all sales for the other employees, excluded emplyee e1.