Weighting budget items from project quarterly dates and options

[DCampbellTSS]

Hello everyone, I am new to DAX and looking for some assistance. 

Currently I am creating a dashboard that pulls in schedule information and cost information such as Budgets and actual cost. With the Budgets I have assigned a option to each of them to use the variable as an option when weighting. I have all of the current data in the dashboard and working correctly. It is pulling all of the projects, hours budgeted for the project, hours spent, and hours remaining. I am trying to figure out how to use the weight option that I have built into the Budget but have Power BI recognize Option 1, Option 2, Option 3, and then apply the weighted percentage of that option to the total amount of hours and then break it down by quarter. I have the date hierarchy and everything established but I am not sure how to organize the custom column. I am really thinking I want to use an IF/OR condition but I am not sure if that is possible with DAX or not. 

I am creating a custom column to create a Schedule Weight and have the different options to select. I figured this would be the easiest to make sure the correct amount of information is added. I think if we have the ability to add more options in the future that would be the best and then I can let my team know what the different options are or change the option names.

Option 1 – Even weight across schedule per quarter of the project. 25%,25%,25%,25%

Option 2 – 50% work in the first quarter and then 10% second, 10% third, 30% fourth quarter.

Option 3 – 75% work in the first quarter and then 0% second, 0% third, 25% fourth quarter.

I would like to use these option examples to weight the amount of hours per quarter. Anyone have any ideas? 

[bhanu_gautam · Accepted solution]

@DCampbellTSS You can create a custom column in your data model to calculate the weighted hours based on the selected option. 

dax
ScheduleWeight =
SWITCH(
TRUE(),
'Table'[Option] = "Option 1",
IF('Table'[Quarter] = 1, 0.25 * 'Table'[TotalHours],
IF('Table'[Quarter] = 2, 0.25 * 'Table'[TotalHours],
IF('Table'[Quarter] = 3, 0.25 * 'Table'[TotalHours],
IF('Table'[Quarter] = 4, 0.25 * 'Table'[TotalHours], 0)))),
'Table'[Option] = "Option 2",
IF('Table'[Quarter] = 1, 0.50 * 'Table'[TotalHours],
IF('Table'[Quarter] = 2, 0.10 * 'Table'[TotalHours],
IF('Table'[Quarter] = 3, 0.10 * 'Table'[TotalHours],
IF('Table'[Quarter] = 4, 0.30 * 'Table'[TotalHours], 0)))),
'Table'[Option] = "Option 3",
IF('Table'[Quarter] = 1, 0.75 * 'Table'[TotalHours],
IF('Table'[Quarter] = 2, 0,
IF('Table'[Quarter] = 3, 0,
IF('Table'[Quarter] = 4, 0.25 * 'Table'[TotalHours], 0)))),
0
)

[DCampbellTSS]

I am getting an expression error that says the name ScheduleWeight isn't recognized. I tried creating a column with the name but it doesn't recognize it.