Project Analysis for a IT project management company
A project services business has one recurring question: is the portfolio growing, and is it growing in the work we actually want. Counting projects answers the first half badly, because ten small low-risk projects and one large complex one are not the same portfolio. So this report tracks value, count and client base together, and breaks all three down by the attributes that describe what kind of work it is.
The report is a single dense overview page backed by a tooltip page and a workings page, rather than a set of tabs. Everything a portfolio review needs is on one screen, with detail arriving on hover.
| Table | Grain and role |
|---|---|
| projects | One row per project. Carries project_key, project_name, value, and the classification attributes: project_type, project_classification, Complexity, Risk Metric, Project Status, Country and nk_org_id for the client. |
| Master Date | Marked date table with Year, Quarter and Month, driving the three stacked slicers and every comparison measure. |
| All Measures | Disconnected table holding all 17 measures, keeping the field list readable. |
Behind the model sit three source files: a project register, a staff list, and a timesheet extract that is by far the largest of the three at around 37MB. Only the project register is loaded into this report, which is a deliberate scoping decision rather than an oversight, and it is the main thing I would revisit.
Column names below are taken from the model. These are the measure definitions as the model computes them, not a paste of the formula bar.
Project Value = SUM( projects[value] ) Project Count = DISTINCTCOUNT( projects[project_key] ) Client Count = DISTINCTCOUNT( projects[nk_org_id] )
Distinct counts rather than row counts, because a project appearing on multiple rows through any future join should not inflate the portfolio.
Every headline number carries two comparisons: against last month for momentum, and against last year for trend. Written as reusable variables so the same pattern applies to all three base measures.
Project Value-Current Month v Previous Month = VAR Previous = CALCULATE( [Project Value], DATEADD( 'Master Date'[Date], -1, MONTH ) ) RETURN [Project Value] - Previous % Project Count-Change From Previous Month = VAR Previous = CALCULATE( [Project Count], DATEADD( 'Master Date'[Date], -1, MONTH ) ) RETURN DIVIDE( [Project Count] - Previous, Previous ) Project Value-% Change From Previous Year = VAR Previous = CALCULATE( [Project Value], SAMEPERIODLASTYEAR( 'Master Date'[Date] ) ) RETURN DIVIDE( [Project Value] - Previous, Previous )
Two measures that turn "what is the biggest thing in here" from a chart into a card.
Project Value-TopValue = MAXX( VALUES( projects[project_key] ), [Project Value] ) Project Value-TopProjectID = VAR Top1 = TOPN( 1, VALUES( projects[project_key] ), [Project Value], DESC ) RETURN CONCATENATEX( Top1, projects[project_name] )
Titles that respond to the slicers, so an exported screenshot still says which month it covers.
Selected Month Name = SELECTEDVALUE( 'Master Date'[Month], "All months" )
Text MoM Project Value =
VAR Delta = [Project Value-Current Month v Previous Month]
RETURN
"Project value is " &
IF( Delta >= 0, "up ", "down " ) &
FORMAT( ABS( Delta ), "#,##0" ) &
" on " & [Selected Month Name]
Seven of the seventeen measures exist purely for formatting: an icon measure and a colour measure for each of project value, project count and client count. This is the pattern worth stealing from this file. The threshold logic lives in the model, so what counts as good is defined once and every card, chart and table picks it up.
Previous Year Comparison Icons-Project Value = VAR Change = [Project Value-% Change From Previous Year] RETURN SWITCH( TRUE(), Change > 0, "▲", Change < 0, "▼", "●" ) Previous Year-Comparision Colour Formating - PV = VAR Change = [Project Value-% Change From Previous Year] RETURN SWITCH( TRUE(), Change > 0, "#2E7D32", Change < 0, "#C62828", "#78829A" )
One page carrying around sixty visual elements, which sounds excessive until you see the structure: a KPI band of cards across the top, each with its icon and colour measure attached, then four donut charts breaking the portfolio down by classification, type, complexity and risk, then area charts for the trend and a matrix for the detail. Year, quarter and month slicers stack down one side.
The four donuts are the analytical heart of it. The same total value, cut four ways, shows whether growth is coming from more of the same work or from a shift into different work. A portfolio growing entirely in high-complexity, high-risk projects is growing, but it is not the same business it was last year.
A tooltip page gives project class and type breakdown on hover without leaving the overview. The workings page holds a matrix used to validate the measures during build, which is good practice: keeping the check visible in the file rather than in a spreadsheet somewhere.
This report describes the portfolio but not its delivery. The timesheet extract sitting unused alongside the project register is the missing half: joining it in would give utilisation, actual hours against estimate, and margin per project rather than just contracted value. That turns the question from "how much work did we win" into "how much of it are we delivering profitably", which is the one a project business actually lives or dies by. The staff file would add the resourcing dimension on top of that.