A Complete Guide to Power BI Dashboard for Projects

Before, we create a dashboard for a project, we need to understand how a project can be represented in numbers or how to evaluate the performance of a project.

• Understanding KPIs of a project by means of animated visuals

• How to quantify a project in terms of cost and time

• What is a S Curve ? Understanding with the help of animation

• Understanding Plan Values, Earned Values , Actual Values (Earned Value Method) etc.

• Cost Performance Index and Schedule Performance Index (CPI and SPI)

• Forecast of a project

This video intends to give an idea about the Power BI dashboard we are going to build and the content of this tutorial series.

• What kind of visuals are in this dashboard

• What are you going to learn from this Course

• Whiz through the course content

Usually, the most important part is the source data which is the basis of all the visualisation. The source data needs cleaning, reshaping, and transforming. And Power BI only needs the datasets required for the visuals. The proper data flow determines the efficiency of the report.

• Major steps in creating Power BI dashboards

• Understanding the data flow from the source to the visuals

This shows basic functionality of the dashboard and and the Interactivity of it.

• Basic understanding on visuals and interactive nature of them

• Basic understanding on data reshaping and transformation

• Basic understanding of the source data 

• Required data inputs for project KPIs evaluation

• How to generate source data

• Understanding data format

• Recording and updating source data

• Summarization and grouping techniques

• The source data file (in excel) is attached herewith. This can be used to practice while studying these tutorials

Power Query allows you to clean, reshape, transform, union, merge data. This is the most efficient way to prepare the optimal dataset required for Power BI. Correct use of Power Query Editor can drastically improve the efficiency and responsiveness of the dashboard.

• Initializing Power Query Editor for Power BI 

• Importing data for Power Query Editor

• Data Transformation - Unpivoting

• Use of steps in Power Query

• Cleaning data

• Duplication of queries for more efficient transformation and reshaping

• Appending or union multiple queries to form one dataset

• Merging queries

• Aggregating data

• Efficient way of data loading from Power Query to Power BI

• Power BI canvas setting (width and height adjustment)

• Formatting canvas

• Understanding how card visuals work

• Formatting card visuals

• Understanding how Power BI slicers work

• Formatting slicer visuals

• Efficient way to add similar visuals

Sometimes Power BI measures can be bit tricky as the return is defined by the context.

• Understanding how Power BI measures work

• Storing all the measure in a separate table

• How a measure works in different contexts

• Concept of aggregation or grouping

• Measures to slice and dice a dataset

• Copy, paste and modify measures

• Understanding the return value of a measure

The critical part of creating a S Curve in Power BI is the measure to calculate the cumulative values based on the date field and to create measures that return values independent of the context. Here, I use animation to explain how the measure that calculate cumulative values works. 

• How to add Power BI chart visuals

• Creating bar charts from measures

• How to write measures for cumulative data based on a date field

• An animated explanation on how a cumulative calculation measure works

• Measures to define visual range of a chart

• Measures whose return do not change with the context

• Offsetting charts

• Producing S-Curves in Power BI

In Power BI , there are so many formatting options. Being familiar with these steps is useful to be more efficient in creating attractive visuals.

• Chart series name

• Formatting chart backgrounds, columns, lines, axis, labels, legend etc.

• Adding dynamic vertical lines at certain x values for charts and formatting

• Flexible way to add a title card

Gauge charts with target and actual values are great way to visualize progress figures.  Additionally, here we use conditional formatting for chart color. It is very easy to spot, if the target is not achieved.   

• What are planned progress and earned progress ?

• Measure to calculate an aggregated value for a defined range of data

• Measure to calculate earned progress and planned progress of the project

• Create gauge charts to indicate earned and planned progress values and formatting them

• Conditional formatting the gauge chart

• Use of permanent filters 

The trend of productivity represents the performance of the project in terms of the cost. This is important when it comes to the forecasting at completion cost.

• What is the productivity factor or the CPI (Cost Performance Index) of a project

• Measures to calculate period and cumulative CPIs

• Formatting a chart with conditional color

A stack histogram in this context is a great way to visualize the built-up of the total resource requirement of the project in time scale. This requires slice and dice the overall measure into categories.

• Animated explanation on manpower calculation and manning histogram of the project

• Measure to calculate manpower numbers from man hours

• Measures to slice and dice the manpower measure into different disciplines

• Creating stack histogram and format it

• Adding overall numbers to a histogram

These visuals are grate way for comparing proportions of a quantity. These then show where the greatest effort / concentration of resources is required to complete the project.

• Creating a donut chart to depict some quantity distribution amongst categories

• Measure that filter data for a dynamic value (a date) of another field

• Creating tree-map chart to represent the distributions and color based on gradient style

To compere current forecast finish dates and target finish dates is always important. This visual gives the ability drill-down into the components which contribute to the overall project delay.

• Measures to calculate last date of a range

• Dates variance calculation

• How to use Unicode Characters

• Formatting table / matrix visuals

In addition to the visuals, these numbers provide quick quantitative representation of the status of the project. Red / Green indicators represents that each number is ok or not.

• Calculating cumulative project KPIs (Earned Value Method)

• Adding conditional indicators to card visuals using UNICHAR function

• Formatting card visuals

• Calculate period project KPIs (Earned Value Method)

• Add conditional indicators to card visuals using UNICHAR function

• Formatting card visuals

It is sometimes confusing in Power BI when multiple selections are active, we do not know what are the selections applied. This video shows how you can resolve that issue.

• Measure to get distinct count from a field

• Measure to concatenate unique values from a field

• Adding card visual to show all the selections / filters applied on the dashboard,

It is usually handy if we have options to select type of charts we need to see. Ex : When we have bar charts and line charts together, we may need to isolate only bar charts. This video shows you how you can achieve that in Power BI.

• Adding new data table for options selection

• Switch function measure that returns a numeric value based on a selection

• Modifying chart data measures in order to switch on and off

You can in fact input data into a Power BI dashboard to factor your source data fields in a measure. This technique can be use to create different analytical tools. In this video, you see two separate examples.

• How to create what if parameters with selected value measures

• Factoring another measure by a dynamic value given by a slider or an input box

• Measure to calculate average values for a dynamic range defined by an input value

• Levelling values on a bar chart for a dynamic range of x value.

This shows how the dashboard we developed looks like when different themes are applied.