Forum Discussion
Bell curve DAX
Solution:
When I was originally trying to do it I was doing it in DAX (calculated table) and couldn’t really pinpoint what was going wrong – the pattern was fine, but the values were not matching – sum of items per day was not equal the actual item number. The values were super small – 0.01 etc. or 3x the actual values. After having a weekend off (I was still thinking about it but not really looking at it) I've decided to do it in Power Query, step by step to have more control over it. Maybe not ideal for some people, but that's what worked for me, our database is not big at all.
Step by step:
- Duplicated my original table, filtered to whatever I needed, deleted columns that were irrelevant.
- Calculated the number of days - Duration.Days([endDate] - [startDate]) + 1
- Made a separate row for every day - List.Dates([startDate], [Days], #duration(1, 0, 0, 0)), expanded the list
- Calculated day number - Number.From([Date] - [startDate]) + 1
- Calculated Mean - ([Days] + 1) / 2
- Calculated StdDev - [Days] / 6
- Calculated WorkloadPercentage / bell curve - 1 / ([StdDev] * Number.Sqrt(2 * Number.PI)) *
Number.Exp(-(([Day Number] - [Mean]) * ([Day Number] - [Mean])) / (2 * [StdDev] * [StdDev]))
- At this point I duplicated this again, grouped by project no. Which created a TotalWorkload for the project.
- I merged the total workload percentage back to the table with all dates.
- I normalised it - [WorkloadPercentage] / [TotalWorkload]
- Loaded it in, created a measure for NoOfItems -
SUMX(
'TableName',
'TableName'[NormalisedWorkloadPercentage] * 'TableName'[noOfItems]
)
And it worked.
Hi sheyyka
It looks as if you have solved the problem yourself.
Could you please share your solution here when time permits? This will benefit other users who are experiencing the same problem.
Thank you in advance for your time and dedication.
Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Solution:
When I was originally trying to do it I was doing it in DAX (calculated table) and couldn’t really pinpoint what was going wrong – the pattern was fine, but the values were not matching – sum of items per day was not equal the actual item number. The values were super small – 0.01 etc. or 3x the actual values. After having a weekend off (I was still thinking about it but not really looking at it) I've decided to do it in Power Query, step by step to have more control over it. Maybe not ideal for some people, but that's what worked for me, our database is not big at all.
Step by step:
- Duplicated my original table, filtered to whatever I needed, deleted columns that were irrelevant.
- Calculated the number of days - Duration.Days([endDate] - [startDate]) + 1
- Made a separate row for every day - List.Dates([startDate], [Days], #duration(1, 0, 0, 0)), expanded the list
- Calculated day number - Number.From([Date] - [startDate]) + 1
- Calculated Mean - ([Days] + 1) / 2
- Calculated StdDev - [Days] / 6
- Calculated WorkloadPercentage / bell curve - 1 / ([StdDev] * Number.Sqrt(2 * Number.PI)) *
Number.Exp(-(([Day Number] - [Mean]) * ([Day Number] - [Mean])) / (2 * [StdDev] * [StdDev]))
- At this point I duplicated this again, grouped by project no. Which created a TotalWorkload for the project.
- I merged the total workload percentage back to the table with all dates.
- I normalised it - [WorkloadPercentage] / [TotalWorkload]
- Loaded it in, created a measure for NoOfItems -
SUMX(
'TableName',
'TableName'[NormalisedWorkloadPercentage] * 'TableName'[noOfItems]
)
And it worked.