Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have the following table :
Employee
EmpId | FullName | Responsible |
1 | Patrick Bateman | |
2 | Dexter Morgan | 1 |
3 | Norman Bates | 2 |
4 | Joe Goldberg | 2 |
which must read liket this :
- Dexter Morgan reports to Patrick Bateman
- Norman Bates and Joe Goldberg report to Dexter Morgan
I have anoter table Goals like this
Goals
GoalId | Goals | EmpId |
4 | 1200 | 4 |
3 | 1200 | 3 |
2 | 2000 | 2 |
1 | 1750 | 1 |
That means :
- Norman Bates must avec 1200 to reach his goal
- But Dexter Morgan doesn't need 2400 to reach his goal but only 2000.
I have a Table as visual with only Goal as Column and I have a filter on Person as Page Filter. Of course, users can filter on one or several persons even no one person.
I want have the following behaviour :
- If no filtrer by user I must display goal for Patrick Bateman (1750) which is the sum of root in path in employee
- If I filter on one user I have to display the goal of the person and not the sum for all poeple who report to him. For instance if I filter on Dexter Morgan, I must display 2000 and not 2400 neither 4400.
- If I filter on several poeple, I mus display the higher person in the hierarchie. For instance if I filter on Patrick Bateman and Dexter Morgan, I should display 1750 and not 3750. But if I filter on Joe Goldberg and Norman Bates, I should display 2400 because Norman Bates doesn't report directly or indirectly to Joe Goldberg and same for Joe Goldberg.
Is that possible to create some calculated measure to have this kind of behaviour ?
Should I created some other column ?
Thans for your support.
OK but how can we had some attached file in this topic ?
You cant directly attach here you need to upload to one drive or dropbox or any cloud service and copy the link to public here
Yes it doesn't work
Ok can you please provide me with some sample data
Did you try using allexcept?
Calculate(sum(table[goal]),allexcept(table,table[id])
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
45 | |
25 | |
19 | |
16 | |
8 |
User | Count |
---|---|
71 | |
49 | |
45 | |
20 | |
17 |