The trading at a bank is managed at book level and there are thousands of books. During the business day, each book moves through several different states starting with ‘Open’, then progressing through ‘Data OK’, ‘Calcs OK’, ‘Approved’, ‘Locked’ and finally ‘Closed’. This process is subject to strict deadlines – and it is important to monitor the overall progress during the day.
The visuals below show two ways of doing this. The bar chart shows the overall progress at a point in time – and it is more useful when it is extended into a side-by-side bar chart that can compare against the previous day or a historic average, so we can get an indication how well the current day is progressing.The stacked area chart shows the progress through the day. The visual below shows at the end of the day – not all books have completed their journey to Closed status which could be a concern.
I’ve simulated in Excel the source data for 100 books with snapshots of the status of each book taken at 30 minute intervals The main table has each book on a row and each column represents a half hourly timestamp.
The book status is represented as an integer from 0 (Open) to 5 (Closed) and this mapping is in a separate table.
The steps to make the charts are as follows
- Import both the Excel sheets into Query Editor
- Unpivot the data to obtain a row for each book at each timestamp
- Load the data. The data model automatically joins the two tables on Status Id
- In the data model, sort the Status Name column by Status Id so that our visualisations order the Status in the proper order rather than alphabetically
- Build the visualisations
I have made a 5 minute YouTube video where I demonstrate building these charts
The Power BI visual is here.
If you would like to recreate it the sample source data is here.