cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
addicted87
Helper I
Helper I

evaluation of sales selected employee vs. Compare Group

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

productsalesemployee
P110E1
P15E2
P115E3
P13E4
P220E2
P210E3
P330E2
P325E4

 

 

What I like to do is now:

 

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

 

productsales selected employee E1sales other employees
P11023
P20 or blank30
P30 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.

1 ACCEPTED SOLUTION
v-yingjl
Community Support
Community Support

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]

employee.png

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.

View solution in original post

4 REPLIES 4
v-yingjl
Community Support
Community Support

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]

employee.png

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.

View solution in original post

amitchandak
Super User
Super User

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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

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_0-1602241283446.png

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.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.