topic Re: Creating an area chart to show total number of hours worked weekly over a set duration in DAX Commands and Tips

Creating an area chart to show total number of hours worked weekly over a set duration

nancymurray — Wed, 31 Jan 2024 16:27:33 GMT

I am a relatively new Power BI user, creating a number of dashboards and reports using Dynamics CRM data, specifically the Opportunities table. For each opportunity, which represents a project, we track the estimated number of hours the project will take, the date it will start and how many weeks duration it will be. From that I have calculated fields to show the week of the year (1 - 52) the project will start and how many hours per week will be required.

The table below is a representation of that data. The column titles are the actual column titles or field names in my data set.

What I need to do is have Power BI take that data and turn it into a weekly schedule of the number of hours that will be worked each week over the duration of the project as shown in the example below.

From that I need to create an area chart that shows a graphical representation of the total hours that will be worked each week across all projects, as shown below.

Once I have the data table, creating the area chart is no problem. Or if I can skip the middle step and go straight from my main data table to the chart, even better. as long as I'm getting the correct weekly totals, I just cannot figure out what formula I need to tell Power BI how to distribute the hours across the right number of weeks. Can anyone shed any light on how to do this?

Thank you.

Re: Creating an area chart to show total number of hours worked weekly over a set duration

Rabi — Wed, 31 Jan 2024 22:23:08 GMT

Hi Mate,

Not sure what you are trying to achieve, Based on your data set you dont really need any DAX to visualise the data.

Below are the steps:

1) Add week name using the dax below:

week Name = "Week" & Sheet1[week number]

2) Visualise as below for the table

3) Visualise as below for the area chart

Please mark it correct if this helped !!

Re: Creating an area chart to show total number of hours worked weekly over a set duration

nancymurray — Wed, 31 Jan 2024 22:38:43 GMT

Thank you Rabi, but this isn't quite what I need. What you've given me is just the hours for the first week of the project. What I need the graph to reflect is all project hours across the full duration of the project. So if you refer to the table I provided in my original post, the first project ABC starts in week 1 and is 40 hours per week for five weeks (for a total of 200 hours), so weeks 1 through 5 should show 40 hours each for that project. Then the next project DEF starts in week 2 and is 10 hours per week for 3 weeks, so weeks 2, 3 & 4 should reflect 10 hours each. The total for each week will be the total number of hours worked for all projects that week, not just the projects that started that week.

The second table I provided is exactly the outcome I need, I just don't know the formula for the field that goes in the Y-axis to make Power BI distribute all the project hours correctly across the full number of weeks it will take to execute the project.

Does that clarify it?

Re: Creating an area chart to show total number of hours worked weekly over a set duration

Rabi — Wed, 31 Jan 2024 23:23:24 GMT

Hi Mate, In that case you will need to use power query.

Below is the exactly what you need

Follow the steps Below:

Import the original data:

Go to Transform data:

click on advanced editor;

use the power query as below:

let Source = Your Source, Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Topic", type text}, {"week number", Int64.Type}, {"EST DURATION (WEEKS)", Int64.Type}, {"Est Probable hours weeks", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Week List", each List.Numbers([#"week number"], [#"EST DURATION (WEEKS)"])), #"Expanded Week List" = Table.ExpandListColumn(#"Added Custom", "Week List"), #"Renamed Columns" = Table.RenameColumns(#"Expanded Week List",{{"Week List", "Week name"}}), #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Week name", Int64.Type}}), #"Renamed Columns1" = Table.RenameColumns(#"Changed Type1",{{"Week name", "Total Weeks"}}) in #"Renamed Columns1"

This Will Definetly work !!

Please mark it Correct !!

Re: Creating an area chart to show total number of hours worked weekly over a set duration

nancymurray — Wed, 31 Jan 2024 23:48:33 GMT

Rabi Yes, this is the exact result I need. For the data source, rather than importing a spreadsheet I am using data from the Opportunity table in our Microsoft Dynamics CRM. So I believe I need to make some adjustments to the first couple lines to correctly identify the data source.

When I opened the advanced editor, it already had source information there, so right now I have the below (I have replaced our org number with x's for confidentiality):

let
Source = CommonDataService.Database("orgxxxxxxxx.crm3.dynamics.com")
dbo_opportunity = Source{[Schema="dbo",Item="opportunity"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Topic", type text}, {"week number", Int64.Type}, {"EST DURATION (WEEKS)", Int64.Type}, {"Est Probable hours weeks", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Week List", each List.Numbers([#"week number"], [#"EST DURATION (WEEKS)"])),
#"Expanded Week List" = Table.ExpandListColumn(#"Added Custom", "Week List"),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Week List",{{"Week List", "Week name"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Week name", Int64.Type}}),
#"Renamed Columns1" = Table.RenameColumns(#"Changed Type1",{{"Week name", "Total Weeks"}})
in
#"Renamed Columns1"

This is giving me an error message that says "Expression.SyntaxError: Token ',' expected." The screenshot below shows where it seems to be expecting a comma (if I'm interpreting the error message properly)

Can you shed any light on how to fix this? Thanks so much.

Re: Creating an area chart to show total number of hours worked weekly over a set duration

Rabi — Wed, 31 Jan 2024 23:52:06 GMT

Hi Nancy,

Try this

let Source = CommonDataService.Database("orgxxxxxxxx.crm3.dynamics.com"), dbo_opportunity = Source{[Schema="dbo",Item="opportunity"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(dbo_opportunity, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Topic", type text}, {"week number", Int64.Type}, {"EST DURATION (WEEKS)", Int64.Type}, {"Est Probable hours weeks", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Week List", each List.Numbers([#"week number"], [#"EST DURATION (WEEKS)"])), #"Expanded Week List" = Table.ExpandListColumn(#"Added Custom", "Week List"), #"Renamed Columns" = Table.RenameColumns(#"Expanded Week List",{{"Week List", "Week name"}}), #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Week name", Int64.Type}}), #"Renamed Columns1" = Table.RenameColumns(#"Changed Type1",{{"Week name", "Total Weeks"}}) in #"Renamed Columns1"

Re: Creating an area chart to show total number of hours worked weekly over a set duration

nancymurray — Thu, 01 Feb 2024 00:12:34 GMT

Rabi thank you so much for your help. This removes the error message. It's now telling me the Topic column of the table wasn't found.

A quick google search tells me the most common reason for this problem is that the column has been removed or renamed, however I have confirmed the column is definitely there and is spelled correctly, it's actually the key field in the table. The data type is text as indicated in the query. I'm at a loss to understand why the query isn't recognizing it.

Re: Creating an area chart to show total number of hours worked weekly over a set duration

Rabi — Thu, 01 Feb 2024 00:14:22 GMT

Hi Nancy, May be just reload the Table and use the power query again !!