Hi,
I am trying to accomplish something, but don't know how to do it.
I have a Dimension (Table) that represents time entries for employees like so :
| Id | EmployeeId | EntryDT | TimeInMinutes | PriceAgreementId |
| 1 | 1 | 2017-03-20 | 100 | 1 |
| 2 | 1 | 2017-03-31 | 50 | null |
| 3 | 2 | 2017-03-21 | 100 | 1 |
| 4 | 2 | 2017-03-23 | 125 | 2 |
| 5 | 3 | 2017-03-15 | 90 | null |
| 6 | 3 | 2017-03-25 | 60 | 1 |
Sometimes they work on "PriceAgreements", and sometimes they don't).
In my Dashboard, i have a Table that groups that table by EmployeeId and Sums the TimeInMinutes. I also have a Slicer for EntryDT :
| EmployeeId | TimeInMinutes |
| 1 | 150 |
| 2 | 225 |
| 3 | 150 |
I need to create 2 new columns that represent :
- The total TimeInMinutes an Employee has worked on all PriceAgreements
So for EmployeeId #1, the Total would be 100. - The total TimeInMinutes ALL Employees have worked, but only for the PriceAgreements the current Employee (current row) has worked on.
So the Table would look like this (without the PriceAgreementIds in parenthesis) :
| EmployeeId | TimeInMinutes | TimeInMinutes on PriceAgreements | TimeInMinutes on PriceAgreements ALL other EmployeeIds |
| 1 | 150 | 100 (PriceAgreementId=1) | 260 (PriceAgreementId=1) |
| 2 | 225 | 225 (PriceAgreementId=1 and 2) | 385 (PriceAgreementId=1 and 2) |
| 3 | 150 | 150 (PriceAgreementId=1) | 260 (PriceAgreementId=1) |
Column "TimeInMinutes on PriceAgreements" is quite easy, but the other one, i cannot find a solution...
I have this DAX expression I started, but it is not complete:
CALCULATE(SUM(TEntry[EmployeeTimeInMinutes]), NOT ISBLANK(TEntry[PriceAgreementId]), ALL(TEmployee))
TEmployee is a Dimension linked to the main Entry Table.
Any help would be appreciated.
Thank you.
