Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello,
I have created the table below as an example of what I need to do. I would like to create a calculated column that behaves like the last column (i.e. I have the first 5 columns and the last one is what I want to create through DAX).
In the Fact table below there are employees (EmployeeID) that work daily on projects (ProjectID). Each project has a unique global scoring. For each date the effort per project is converted to a Resource Allocation % per project (already done in the table below for simplicity). I would like to create a cumulated daily resource allocation per employee, but it needs to be sorted by the project scoring. Hope the table explains it. I think the WINDOW function is the way to go but not sure.
OPTIONAL: the project scoring is a global number contained in a different Dim table where each project is (DIM_Project). I've included the project scoring in the table below to make it easier, and also because I don't know whether my ask may work without the scoring in the same table. If the answer could contain 2 solutions, one for the situation where the project scoring is in the same table as per below and one where the project scoring is in a different table as in the reality it would be much appreciated. I would prefer the one with the scorim in the other table but if it is not possible then with the scoring in the same table is fine.
Thank you very mcuh in advance for your help, really appreciated 🙂
EmployeeID | AllocationDate | ProjectID | Project Scoring | Resource Allocation (%) | Daily Running ResAll (%) |
1 | 21-Jul-24 | 638133 | 85 | 10 | 10 |
1 | 21-Jul-24 | 764798 | 35 | 40 | 50 |
1 | 21-Jul-24 | 764800 | 15 | 50 | 100 |
1 | 22-Jul-24 | 638133 | 85 | 10 | 10 |
1 | 22-Jul-24 | 764798 | 35 | 40 | 50 |
1 | 22-Jul-24 | 764800 | 15 | 50 | 100 |
1 | 23-Jul-24 | 638133 | 85 | 10 | 10 |
1 | 23-Jul-24 | 764798 | 35 | 80 | 90 |
1 | 23-Jul-24 | 764800 | 15 | 50 | 140 |
1 | 24-Jul-24 | 638133 | 85 | 10 | 10 |
1 | 24-Jul-24 | 764798 | 35 | 80 | 90 |
1 | 24-Jul-24 | 764800 | 15 | 50 | 140 |
2 | 20-Jul-24 | 638133 | 85 | 20 | 20 |
2 | 20-Jul-24 | 764798 | 35 | 40 | 60 |
2 | 20-Jul-24 | 764800 | 15 | 50 | 110 |
2 | 21-Jul-24 | 638133 | 85 | 20 | 20 |
2 | 21-Jul-24 | 764798 | 35 | 40 | 60 |
2 | 21-Jul-24 | 764800 | 15 | 50 | 110 |
2 | 22-Jul-24 | 638133 | 85 | 20 | 20 |
2 | 22-Jul-24 | 764798 | 35 | 40 | 60 |
2 | 22-Jul-24 | 764800 | 15 | 50 | 110 |
2 | 23-Jul-24 | 638133 | 85 | 10 | 10 |
2 | 23-Jul-24 | 764798 | 35 | 80 | 90 |
2 | 23-Jul-24 | 764800 | 15 | 50 | 140 |
2 | 24-Jul-24 | 638133 | 85 | 10 | 10 |
2 | 24-Jul-24 | 764798 | 35 | 80 | 90 |
2 | 24-Jul-24 | 764800 | 15 | 50 | 140 |
Solved! Go to Solution.
Source Data
Sequence number to sort project scoring keeping employee id and date intact.
Cumulative daily allocation calculation based on the sequence number (ascending) for a fixed date and emp id.
If this solves your problem then please accept the same as your solution.
Source Data
Sequence number to sort project scoring keeping employee id and date intact.
Cumulative daily allocation calculation based on the sequence number (ascending) for a fixed date and emp id.
If this solves your problem then please accept the same as your solution.
Thanks @Rakesh1705 , much appreciated
I think the intermediate step of the order is not needed. It can work directly with the scoring (also needs the bigger than to change as a consequence). Like this:
hello @Jo77
please check if this acomodate your need.
create a calculated column.
Cumulative =
SUMX(
FILTER(
'Table',
'Table'[EmployeeID]=EARLIER('Table'[EmployeeID])&&
'Table'[ProjectID]=EARLIER('Table'[ProjectID])&&
'Table'[AllocationDate]<=EARLIER('Table'[AllocationDate])
),
'Table'[Resource Allocation (%)]
)
For project scoring, form what i can see in your sample data, project scoring is same for projectID (i.e 764800 always has project scoring 15). Unless this is wrong, the cumulative can be calculated from projectID.
Hope this will help you.
Thank you.
Thank you very much @Irwan , really appreciate your time
Unfortunately it doesn't do what I would need. At the bottom of this response you can see how what it does is to cumulate by project and then reset when the EmployeeID changes (I've sorted the columns in the same way as in my original example for comparison). As per my original example, every day should reset the cumulation that is done project after project within the day for a given EmployeeID. Every EmployeeID has its own count too in theory, but the EmployeeID doesn't really matter other than to make sure that the allocations within a day don't keep cumulating regardless of the EmployeeID.
Also, to your query on the scoring, it is very important that the cumulation follows the order of priority of the scoring (higher scoring = higher priority). You can see that in my original example. To give you further context, the next step I want to do is to tag the rows where the cumulation is more than 100. This will mean that the employee will not be able to attend to that project that day. This is why the scoring (=priority of the project) is important. Only the lowest priority projects should get the highest numbers of cumulation as they should be at the bottom of the proiority pile. That's why originally I was maybe thinking about the WINDOW-type of function as they allow the ORDERBY/PARTITIONBY type of parameters, but I cannot make it work.
Hope the above helps explain. If you please look at my original example with this in mind hopefully it makes more sense. A big massive thank you again @Irwan
This is the result of inputting your code (scoring is different as this comes from the actual data, the one before was modified slightly for simplicity):
hello @Jo77
do you mind to copy and paste your sample data so i can copy your sample data? not in image format.
You can certainly use WINDOW, but first thing is you need to explain your need/goal/limitation in calculation.
So, I am going to clarify your need.
You want to calculate daily cumulative of Resource Allocation for each EmployeeID for each PPM-ID for same Project Score? This will reset when different employeeID, different PPM-ID, and different Project Score. And if project score is different, it will be calculated differently?
Is this correct?
Or you just want to daily cumulative for different Project Score only? This will reset when Project score is different.
Sorting Project Score can do later with indexing or sort in visualization.
Thank you.
Hello @Irwan , thanks again
To not exceed the limits of the post I will put the explanation on the objective here in this post and then reply to it with the data if that's ok.
The objective is to have a way to know which projects cannot be done because they have employees in them that are overallocated (i.e. that they are above their 100% allocation). I put an improved view of my example below, it is a pic to not use characters and exceed the limit of the post.
The columns in green is the basic info. The ones in red are the ones I already have but, for this purpose, bring to the wrong conclusion. The ones in gray is where I want to go (only asking for the first gray column here in the forum).
As you can see in the red columns, I have a way to know the daily % allocation of an employee and therefore whether he/she is overallocated on that day. However, I cannot then go and tag the projects by the IsOverallocated column as it is not the real view. It is not because the projects that fit in the less than 100% allocation will get actually done, only the ones with a cumulative allocation % beyond 100% on the day will not get done. Which ones are they though? Here is where the project priority comes in and why the cumulative sum of daily allocation % per employee and day needs to be done in the project scoring order. To make sure that the ones that go above 100% are the ones with the lower priority and then only those are tagged in the column NEW IsOverallocated as being part of the overallocation.
Hope that explains it better, and shows why I have the concept of windows, sortby, partitionby in my head as the potential solution (from my small SQL days). Sorry for not having gone to this depth before and another massive thank you for your great help @Irwan
Apologies @Irwan but I'm trying to post the data. It shows as posted but then it is not. If I refresh or if I look at it from my phone it is just not there. Not sure how to do it, I'll keep trying.
Here you have it I was getting some HTML issue that I cannot get around. I'm sending it in a way that hopefully you can use the space as separator to put it back together. Apologies, thanks again for your help @Irwan.
EmployeeID AllocationDate PPM-ID Project score ResourceAllocation Resource Allocation (%)
100003 21/07/2024 638133 58.2 0.75 10
100003 21/07/2024 764800 48 3.75 50
100003 21/07/2024 764798 35.25 3 40
100003 22/07/2024 638133 58.2 0.75 10
100003 22/07/2024 764800 48 3.75 50
100003 22/07/2024 764798 35.25 3 40
100003 23/07/2024 638133 58.2 0.75 10
100003 23/07/2024 764800 48 3.75 50
100003 23/07/2024 764798 35.25 3 40
100003 24/07/2024 638133 58.2 0.75 10
100003 24/07/2024 764800 48 3.75 50
100003 24/07/2024 764798 35.25 3 40
100003 25/07/2024 638133 58.2 0.75 10
100003 25/07/2024 764800 48 3.75 50
100003 25/07/2024 764798 35.25 3 40
100003 26/07/2024 638133 58.2 0.75 10
100003 26/07/2024 764800 48 3.75 50
100003 26/07/2024 764798 35.25 6 80
100003 27/07/2024 638133 58.2 0.75 10
100003 27/07/2024 764800 48 3.75 50
100003 27/07/2024 764798 35.25 6 80
100005 21/07/2024 638133 58.2 1.5 20
100005 21/07/2024 764800 48 2.25 30
100005 21/07/2024 764798 35.25 1.875 25
100005 22/07/2024 638133 58.2 1.5 20
100005 22/07/2024 764800 48 2.25 30
100005 22/07/2024 764798 35.25 1.875 25
100005 23/07/2024 638133 58.2 1.5 20
100005 23/07/2024 764800 48 2.25 30
100005 23/07/2024 764798 35.25 1.875 25
100005 24/07/2024 638133 58.2 1.5 20
100005 24/07/2024 764800 48 2.25 30
100005 24/07/2024 764798 35.25 1.875 25
100005 25/07/2024 638133 58.2 1.5 20
100005 25/07/2024 764800 48 2.25 30
100005 25/07/2024 764798 35.25 1.875 25
100005 26/07/2024 638133 58.2 1.5 20
100005 26/07/2024 764800 48 2.25 30
100005 26/07/2024 764798 35.25 1.875 25
100005 27/07/2024 638133 58.2 1.5 20
100005 27/07/2024 764800 48 2.25 30
100005 27/07/2024 764798 35.25 1.875 25
User | Count |
---|---|
25 | |
12 | |
8 | |
6 | |
6 |
User | Count |
---|---|
26 | |
12 | |
11 | |
8 | |
7 |