Power BI – ABC Automotive Ltd. (Part 3 – Sales Network Reports)

Using the “demo” database, which I described in Part 1 of this set of blog entries, I have created a number of sample reports using Power BI Desktop focusing on the Dealer network of the Financial Services Division of a fictitious company, ABC Automotive Ltd.

DEALER NETWORK MAP
Image 1: Dealer Network Map for ABC Automotive Ltd. created using Mapviewer 

The primary focus of these reports is to demonstrate how a Sales Network can be fully integrated into the reporting for any company providing business insights that are based on geographic areas.

This Sales Network that has been created for ABC Automotive Ltd. is based on the common standard for the Automotive Sector in the UK (Regions / Areas / Dealers).

In addition there are maps showing UK Postcode Districts as it is also common for the Dealer areas to be defined on a Postcode District basis.  This has been done for the sales network of ABC Automotive Ltd.

All of the map visualisations that are seen on the various reports have been created using the GIS software, Mapviewer .  This software is used to produce the “custom network maps” in the “ESRI Shapefile” format that will then be converted into “TopoJSON” formatted files using a tool like mapshaper

These “TopoJSON” files can then be imported into Power BI using the “Shape Map” visualisation and linked to information sources that contain the relevant geographic reference data. 

Click HERE to open up the interactive reports in a new tab.


ABC Automotive - Hints
Hints: Three hints on how to select the display of information within the visualisations i.e. in addition to using the filters/slicers at the top of the page.


ABC Automotive - Network Regions Report
Image 2: A Network Regions report containing a number of different visualisations including a map showing the relative number of agreements within the geographical areas of the 2 Sales regions for ABC Automotive Ltd. (North / South).

Of particular interest are the two matrix table visualisations that link Experian Mosaic data to agreements data and displays this information on the custom map.


ABC Automotive - Network Areas Report
Image 3: A Network Areas report containing a number of different visualisations including a map showing the relative number of agreements within the geographical areas of the 13 Sales areas for ABC Automotive Ltd. (“AREA01” – “AREA13”).


ABC Automotive - Network Dealers ALL ReportImage 4: A Network Dealer Areas report containing a number of different visualisations including a map showing the relative number of agreements within the geographical areas of the 32 Sales Dealer Areas for ABC Automotive Ltd. (“DLR001” – “DLR032”).

ABC Automotive - Network Dealers Report
Image 5: A Network Dealer Areas report containing a number of different visualisations including a map showing the relative number of agreements within the geographical areas of the selected Sales Dealer Areas for ABC Automotive Ltd. (“DLR001” – “DLR006”).


ABC Automotive - Network Postcode Districts Report
Image 6: A Postcode District report containing a number of different visualisations including a map showing, in this particular report, the relative number of agreements for the Royal Mail Postcode District geographical areas for the selected Postcode Area (“RG” – Reading, UK).

ABC Automotive - Network Postcode Districts Report 2
Image 7: Another Postcode District report containing a number of different visualisations including two different map visualisations showing, in this particular report, the relative number of agreements for the Royal Mail Postcode District geographical areas for the selected Postcode Area (“RG” – Reading, UK).


ABC Automotive - Network Postcode Districts DLR009 Report
Image 8: A Postcode District report containing a number of different visualisations including a map showing, in this particular report, the relative number of agreements for the Royal Mail Postcode District geographical areas for the selected Network Dealer (“DLR009”).

ABC Automotive - Network Postcode Districts AREA06 Report
Image 9: A Postcode District report containing a number of different visualisations including a map showing, in this particular report, the relative number of agreements for the Royal Mail Postcode District geographical areas for the selected Network Area (“AREA06”).


Here’s hoping that this was of interest.  If you have any questions feel free to contact me at

Power BI – ABC Automotive Ltd. (Part 2 – Financial Services Reports)

Using the “demo” database, which I described in Part 1 of this set of blog entries, I have created a number of sample reports using Power BI Desktop for various areas of the Financial Services Division of a fictitious company, ABC Automotive Ltd.

The “demo” database, together with other data, were imported into Power BI Desktop and, where necessary, were transformed using the ETL, or transformation, functions that are embedded in the desktop / online Power BI software (Power Query / “M” language / “DAX” language ).

Having now got the measure of uploading the Power BI Desktop file (.pbix), via OneDrive Business, to my online Power BI Service account it is possible to interactively query the various reports that are described below.

Click HERE to open up the interactive reports in a new tab.


ABC Automotive - Hints
Hints: Three hints on how to select the display of information within the visualisations i.e. in addition to using the filters/slicers at the top of the page.


ABC Automotive - Asset Report
Image 1: Asset report containing a number of different visualisations of data related to the asset being financed.  It is possible, in this report, as well as in the others, to filter the finance agreement data using Geographical, Asset and Finance Product criteria. 

The pie and donut charts offer the ability to drill down through the data e.g. from Make to Model to Fuel Type.  The matrix visualisation displays the data that is held in the “demo” database that I have created.


ABC Automotive - Finance Report
Image 2: Finance report containing a number of different visualisations of data related to financial transactions.  Of particular interest are the KPI visualisations that provide a easily viewed status of some of the financial figures for the filtered finance agreements.


ABC Automotive - Customer Report
Image 3: Customer report containing a number of different visualisations of data related to the customer.  Of particular interest is the Treemap visualisation showing the link between the agreement data and external Experian Mosaic customer demographics data.


ABC Automotive - Residual Values Report
Image 4: Residual Values report containing a number of different visualisations of data related to the residual value of the assets.  Of particular interest is the “RV Change Percentage” filter/slicer which allows the user to select a percentage figure that will the dynamically change the displayed residual value totals for all financed agreements.

If a user selects the “Fuel Type” of “Diesel” and selects a percentage change figure  it would be possible to see what the reduction in residual values would be in the latest “anti-diesel” climate here in the UK.


ABC Automotive - Arrears Report
 Image 5: Arrears report containing a number of different visualisations of data related to customer arrears.


ABC Automotive - PCP Payments Diagram
Image 6: A diagram showing the amounts of deposit, monthly payments and residual value (GMFV) for the filtered agreements.


Here’s hoping that this was of interest.  If you have any questions feel free to contact me at fred.williams@insightfuldatasolutions.com

Power BI – ABC Automotive Ltd. (Part 1 – Access Database)

Having got the hang of #POWERBI I have decided to combine my knowledge of the Automotive sector with my newly acquired knowledge of Microsoft’s Power BI.

However, before I could start to develop reports I needed to have a source of meaningful data for Power Bi to report on.  This data could not be “live” data from an Automotive Company so I had to develop my own representative “demo” database for a fictitious company, ABC Automotive Ltd., based on my understanding of the Automotive sector.

To this end, I have developed a MS Access 2016 database that can dynamically generate a portfolio of 100,000+ Automotive Finance agreements.  The database is very comprehensive and can be weighted to mirror the finance portfolio of any Automotive Finance company.

The “demo” portfolio can be weighted according to the following criteria:

1. Vehicle Manufacturer (Audi, BMW, Honda etc.)
2. Vehicle Type (Car, Bike, Van etc.)
3. Vehicle Status (New, Used)
4. Vehicle Fuel Type (Petrol, Diesel, Other)
5. Vehicle Categorisation/Segmentation (Private/B SUPERMINI 5DR Hatch etc.)
6. Vehicle Registration Date (to cater for the two annual peaks in the UK) 
7. Finance Product Type (HP, PCP, LP)
8. Financial Data (vehicle cost, deposit, monthly payments, arrears, residual values)
9. Agreement Status (Financed, Closed, Litigation, Arrears etc.)
10. Agreement Start Date (to cater for the two annual peaks in the UK) 
11. Geographic data (Sales Networks, Customers, Assets etc.)
12. Customer Demographics (Age, Family Status, Gender, House Owner etc.)
….. and other criteria

For example, it would be possible to create a “demo” portfolio for a particular Manufacturer Finance Company that contains 95% of agreements for their own vehicles and 5% for other manufacturers vehicles with 5% of agreements being in arrears and a defined Finance Product split.

Alternatively, it would be possible to create a “demo” portfolio for a non-Manufacturer Finance Company that contains a more even distribution of agreements for the cars of all manufacturers with 15% of agreements being in arrears and a majority of Hire Purchase agreements. 

The version that I will be using to create the Power BI reports that will be documented in later blogs will contain an distribution of manufacturer vehicles that match recent registration statistics, with 80% PCP products, 10% arrears and an even distribution of Vehicle Registration and Agreement Start dates.

The “demo” database will continue to be added to with the next major addition being the generation of cash flows ….. not going to be easy as it will depend on randomising payments (but not too much) for randomised agreements 🙂

It would have been possible to create most of the database using the ETL functionality and the embedded “M” language in Power BI Desktop but it was quicker for me to create it in MS Access 2016 (decades of experience) and not in the desktop (one years’ experience). 

ABC Automotive - MS Access Database
Image 1. MS Access creation of the “demo” database


This blog entry will be followed, in the very near future, by the following:

Part 2 – Power BI reports for the “Financial Services Division” of ABC Automotive Ltd.
 
Part 3 – Power BI reports for the “Financial Services Division” of ABC Automotive Ltd. (focussing on geographical information for Dealer Network, Customers & Assets)

Part 4 – Power BI reports for the “Sales Division” of ABC Automotive Ltd.


Here’s hoping that this was of interest.  If you have any questions feel free to contact me at fred.williams@insightfuldatasolutions.com

 

 

Power BI – Mapping when “Disconnected”

Having recently read an article on the subject (Aaron Bator MSFT, Click here for his blog entry) I decided to share my own similar approach to providing “geographical” visualisations in a report.

I am a member of a team working on the creation of a Neighbourhood Plan for the Parish, Tilehurst, in which I live.  As part of my input to the plan I have been investigating how to visualise all manner of demographic data (from the Office for National Statistics) on standard and custom maps.

I, like Aaron, looked at the current options in Power BI Desktop and found none of them to be what I was looking for.  The need to be connected to the Internet was one issue as was the lack of any serious capability to use my own “custom regions”.  

Being a big fan of the “Synoptic Panel” Power BI Desktop visualisation and it’s sister the “Synoptic Designer” website (both by OKViz) I decided to create the custom maps that I needed as follows:

1. GIS software (Mapviewer 8)
2. Export the created map as a “.png” file
3. Upload the “.png”
to the “Synoptic Designer website
4. Define the areas and then export the results as a “.svg” file
5. Add the “.svg” file(s) to a Report using the “Synoptic Panel” visualisation 
6. Link the visualisation(s) to the data table


Office for National Statistics (ONS) Population Report for Tilehurst Parish

Image 01Image 1: Tilehurst Parish population by ONS Lower Layer Super Output Area (LSOA), Middle Layer Super Output Area (MSOA) and Output Area (OA).

Click HERE to open up the interactive report in a new tab!


Image 02
Image 2: Map showing the population of Tilehurst Parish by Ward and Output Area (OA)


Image 03
Image 3: Zooming in to map showing the population of Tilehurst Parish by Ward and Output Area (OA).  Note the use of the “States” settings to colour the Output Areas by population.


Image 04
Image 4: Map showing the population of Tilehurst Parish by Middle Layer Super Output Area (MSOA)


Image 05
Image 5:  Map showing the population of Tilehurst Parish by Lower Layer Super Output Area (LSOA)


Image 06
Image 6: Source ONS Census 2011 data consolidated by Geolytix


Image 07
Image 7: Data model showing Geolytix and Postcode tables


Here’s hoping that this was of interest.  If you have any questions feel free to contact me at fred.williams@insightfuldatasolutions.com

I will describe how I create the maps using the GIS software (Mapviewer 8) and the  Synoptic Designer website” soon.

 

 

 

 

Power BI – Visualisation – Org Chart

Spent some time experimenting with the excellent “Synoptic Panel by OKViz” custom visualisation and came up with the idea of finding a different way to visualise the costs associated with a companies organisation structure.

Organisation Chart & Associated Salaries and Costs Report

Organisation Chart 01
 Image 1: Report showing Total Salaries and Costs for a fictitious organisation.  The Organisation Chart diagram in the center is an SVG file that has been added to the report using the “Synoptic Panel by OKViz” custom visual.

Click HERE to open up the interactive reports in a new tab.


Organisation Chart 02
Image 2:  The Organisation Chart that has been added to the report using the “Synoptic Panel by OKViz” custom visual.  The chart is linked to a structured table that contains descriptions of the roles and the associated salary and costs.  The colours indicate in which range/state the salary sits.


2017-04-01_161221
Image 3:  The table that is linked to the various visualisations in the report.


Organisation Chart 4
Image 4:  A “Stacked Column Chart” showing the Associate Salary Total by Department and Sub-Departments.


Organisation Chart 5
Image 5:  A “Pie Chart” showing the Associate Salary Total by Department.


Organisation Chart 6
Image 6:  A “Treemap” showing the Associate Salary Total by Department.


Organisation Chart 7
Image 7:  A “Matrix Table” showing the table of Associates Data with Totals.


Organisation Chart 2
Image 8:  A “Table” showing the table of Associates Data.


Organisation Chart 09
 Image 9:  Report showing Total Salaries and Costs for a fictitious organisation.  All the visualisations are filtered by the selections in the “Hierarchy Slicer”.


Organisation Chart 10
 Image 10:  The Organisation Chart reflecting the filtering by the selections in the “Hierarchy Slicer”


Organisation Chart 11
 Image 11:  The Organisation Chart that has been created using Microsoft Visio Pro that is exported as a “.png” file which can then be uploaded to the “SYNOPTIC DESIGNER FOR POWER BI” at the web address, http://synoptic.design/.


Organisation Chart 11
 Image 12:  The Organisation Chart that has been uploaded to the “SYNOPTIC DESIGNER FOR POWER BI” at the web address, http://synoptic.design/ before being exported as a “.svg” for use in the “Synoptic Panel by OKViz” custom visual.


Here’s hoping that this was of interest.  If you have any questions feel free to contact me at fred.williams@insightfuldatasolutions.com

Power BI – Visualisation – Calendar

I am very impressed by the “Calendar” custom visual created by Andy Cross (@andyelastacloud) but having spent some time experimenting with the excellent “Synoptic Panel by OKViz” custom visualisation I missed the ability to define up to 5 states for the calendar data and also wanted to have the visualisation look more like a calendar. 

I therefore decided to develop my own approach to display data on calendars as I had some time to spare and wanted to experiment with Power BI.

Following is a description of my first attempt


Power-BI-Calendar-Image-1
 Image 1: Report showing Calendars for the years 2016, 2017 and 2018 with the associated source data table below (in this case a Calendar Table with a column containing a test amount for display purposes).  The calendars are SVG files that are added to the report using the “Synoptic Panel by OKViz” custom visual.

Click HERE to open up the interactive report in a new tab.

Click HERE to open up, in a new tab, a new version of the interactive report which demonstrates the ability to easily create calendars in other languages.


Power-BI-Calendar-Image-2Image 2: Calendar for 2016 (in focus mode) showing the day of the month with fill colours dependant on the range of values defined in the 5 States that are available in the “Synoptic Panel by OKViz” custom visual.


Power-BI-Calendar-Image-3Image 3: Calendar for 2018 (in focus mode) showing the day of the month and data value with fill colours dependant on the range of values defined in the 5 States that are available in the “Synoptic Panel by OKViz” custom visual.


INTERESTED?

If you are interested in having copies of the Calendars (other language versions will be considered – depends on how much free time I have) then send me an email at fred.williams@insightfuldatasolutions.com

I might also add them later to the Gallery at http://synoptic.design/ (need to investigate whether that would be possible).


Following is a brief description of the steps that I took to create the Calendars (for those of you who are interested) 

Step 1:

Create a calendar in the desired language using Microsoft Visio and export as a png file.

Power-BI-Calendar-Image-4


Step 2: 

Upload the png file exported from Microsoft Visio to the website http://synoptic.design/

Now spend a not short period of time creating index names for all the days in the year (365/366)!  it is recommended that the index names have the format YYYYnn as this can be automatically created when creating a Calendar table (which is to be linked to the source data using the date field).   The second box (Area Name for display) needs to be updated with the number of the day within the month for display in the final calendar.

Power-BI-Calendar-Image-6

When this task has been completed click on the “EXPORT TO POWER BI” button to save the SVG file on your PC.


Step 3:

Add the “YearDayOfYear” column to the Category field, the “Calculated Test Amount” column to the Measure field and any fields that you want to the Tooltips field. 

Power-BI-Calendar-Image-8

Add the required ranges to the State fields.

Power-BI-Calendar-Image-9


….. and here is the final report:

Power-BI-Calendar-Image-7


Here’s hoping that this was of interest.  If you have any questions feel free to contact me at fred.williams@insightfuldatasolutions.com