Bell curve DAX

[sheyyka]

Hi,

Can anyone help with creating a bell curve (will be used in a stacked area chart) for workload planning? 

My columns:

projectID | startDate | endDate | noOfItems | days 

The idea is to have something like the screenshot below, but I can't get the values to be correct. 

I've been trying to make it work for the last few days and I'm slowly losing my mind. Thanks in advance! 🙂

EDIT: I've managed to sort it out, I think I just needed a rest as I was staring at it for too long. Thanks!! 🙂

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.  

[Anonymous]

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.

[sheyyka · Accepted solution]

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.  

[sheyyka]

Yeah sure, the is the data roughly:

[PhilipTreacy]

@sheyyka 

We need your data to do anything .....

Phil