Jun 09 2021 03:01 AM
Hi
In my role in local government I work with businesses and developers to encourage sustainable travel. Within this I help with the delivery of travel surveys which are used to monitor changes in the modes of transport that are being used.
I have traditionally used Excel for analysis and reporting but I am now starting to make use of PowerBI for this.
I am wondering if there is a way in Excel and PowerBI to calculate CO2 emissions for a journey where some of the elements in the calculation would be determined by the mode of transport used.
I have a table with details of journeys broken down into stages to allow for people making use of different transport modes in the course of their journey:
Mode 1 | Time 1 | Destination 1 | Other 1 | Mode 2 | Time 2 | Destination 2 | Mode 3 | Time 3 | Mode 4 | Time 4 | Destination 4 |
Drive a car alone | 25 | My place of work | |||||||||
Drive a car alone | 45 | My place of work | |||||||||
Cycle | 45 | My place of work | |||||||||
Cycle | 60 | My place of work | |||||||||
Taxi | 15 | My place of work | |||||||||
Car share as a driver | 15 | My place of work | |||||||||
Car share as a passenger | 10 | My place of work | |||||||||
Taxi | 25 | My place of work | |||||||||
Car share as a driver | 30 | My place of work | |||||||||
Motorcycle (above 125cc) | 20 | My place of work | |||||||||
Car share as a passenger | 20 | My place of work | |||||||||
Motorcycle (above 125cc) | 15 | My place of work | |||||||||
Walk | 5 | The bus stop | Bus | 25 | The bus stop | Walk | 5 | My place of work | |||
Walk | 15 | My place of work | |||||||||
Walk | 20 | The bus stop | Bus | 10 | My place of work | ||||||
Walk | 15 | The bus stop | Bus | 35 | The bus stop | Walk | 15 | My place of work | |||
Walk | 5 | The bus stop | Bus | 25 | The bus stop | 0 | Walk | 10 | My place of work |
I also have a table which contains the information on average speeds and the average CO2 emissions per km:
Mode Average Speeds, expressed as kilometres per hour: | CO2 Factors. These are expressed as kilogrammes of CO2 per passenger per kilometre: | ||||
Walk | 4.80 | Underground | 0.065 | ||
Cycle | 20.0 | DLR | 0.075 | ||
Underground | 33.0 | Tram | 0.042 | ||
DLR | 25.0 | Commuter Train | 0.062 | ||
Tram | 22.7 | Bus | 0.081 | ||
Commuter Train | 40.0 | Drive a car alone | 0.206 | ||
Bus | 15.5 | Taxi | 0.168 | ||
Drive a car alone | 26.5 | Scooter | 0.073 | ||
Taxi | 26.5 | Motorcycle (above 125cc) | 0.112 | ||
Scooter | 26.5 | Riverboat | unknown | ||
Motorcycle (above 125cc) | 26.5 | Car share as a driver | 0.129 | ||
Riverboat | 19.0 | Car share as a passenger | 0.129 | ||
Car share as a driver | 26.5 | Motorcycle (below 125cc) | 0.112 | ||
Car share as a passenger | 26.5 | ||||
Motorcycle (below 125cc) | 26.5 | ||||
Park and Ride | 15.5 |
The CO2 is calculated by:
1. Multiplying the time taken by the average speed for the mode used (e.g. 25*26.5) and divide by 60 (to allow for conversion from minutes to hours) to give distance travelled (expressed in kilometres).
2. multiply the distance travelled by the corresponding “CO2 Factor”. This will give kilogrammes of CO2 for each mode travelled.
What I would like to be able to do is automatically calculate the relevant CO2 amount for each row where the average speed and the CO2 Factor will change depending on the mode of transport used.
Does anyone know if it is possible to run a calculation like this in Excel or PowerBI and if so how it would need to be written to take into account the different multipliers to be used?
Many thanks in advance,
Andy
Jun 09 2021 08:16 PM
Jun 09 2021 09:36 PM
Jun 10 2021 01:03 AM
Hi @StoneKiwi
I have attached the spreadsheet that I have been working with.
Any help you can provide will be most helpful.
Andy
Jun 10 2021 01:07 AM
Hi
Thanks for the reply.
What I would like to be able to show is the amount of CO2 produced by each mode of transport that produces this.
So for instance if it is possible to do so car should show the amount of CO2 as a combination of Drive a car alone, Car share as driver and Car share as passenger.
As this is for reporting survey outcomes a simple bar chart would suffice.
I have attached the spreadsheet that I have been working with.
Jun 10 2021 07:16 AM
Jun 10 2021 07:45 AM
I hadn't got as far as anything to output the results in other than with a Pivot table and chart as in the attached. I had moved on to using PowerBI for this but need to have the ability to do this in Excel as a fall back especially if someone else takes on this work
Jun 10 2021 10:26 AM
Solution
Hi there,
So I took a stab at it and looked like you were doing a lot of manual things to get the calculations going. First thing I did is change up the formulas in column E,I, L, & P. I put in an index/match formula that will crawl through the "Factors" sheet to find the respective CO2 emissions (factors 1 & 2). With different data entries, you'll need to update these values for the fields in the raw data to compute properly. Secondly, because there are mulitple methods of transportation that vary from 1 - 4 selections, I added in a column (R) to find the main method of transportation (I used the max time of commute among the values in the row). Column R will spit back the main method of transportation. And lastly (regarding the column changes), added in 1s for column T to count the unique survey participants.
I formatted everything as a table, so the pivot will reference the table instead of a range (this will help you avoid having to update the pivot range each time an entry is made).
If you enter in new data, just click the refresh button on the data tab.
I would recommend that you finalize the survey and export in the rawest form and then build formulas around on new columns on the right. That will make it much easier if you anticipate thousands of incoming surveys.
The pivot chart will show the total CO2 emission by individuals (using their primary form of transportation) along with the count of survey takers. You could also show averages, but in this case, I think sheer volume might be the way to go. Secondary axis will show the count of unique survey takers.
Try keying in data and then hitting refresh to make sure things don't break. The "Factors" sheet will be your source of truth so anything missing will throw off the calculations (i.e., adding in a new type of transportation without updating the "factors" sheet will break the formulas)
Hopefully this helps, let me know if you have any questions.
Jun 11 2021 05:00 AM
Jun 10 2021 10:26 AM
Solution
Hi there,
So I took a stab at it and looked like you were doing a lot of manual things to get the calculations going. First thing I did is change up the formulas in column E,I, L, & P. I put in an index/match formula that will crawl through the "Factors" sheet to find the respective CO2 emissions (factors 1 & 2). With different data entries, you'll need to update these values for the fields in the raw data to compute properly. Secondly, because there are mulitple methods of transportation that vary from 1 - 4 selections, I added in a column (R) to find the main method of transportation (I used the max time of commute among the values in the row). Column R will spit back the main method of transportation. And lastly (regarding the column changes), added in 1s for column T to count the unique survey participants.
I formatted everything as a table, so the pivot will reference the table instead of a range (this will help you avoid having to update the pivot range each time an entry is made).
If you enter in new data, just click the refresh button on the data tab.
I would recommend that you finalize the survey and export in the rawest form and then build formulas around on new columns on the right. That will make it much easier if you anticipate thousands of incoming surveys.
The pivot chart will show the total CO2 emission by individuals (using their primary form of transportation) along with the count of survey takers. You could also show averages, but in this case, I think sheer volume might be the way to go. Secondary axis will show the count of unique survey takers.
Try keying in data and then hitting refresh to make sure things don't break. The "Factors" sheet will be your source of truth so anything missing will throw off the calculations (i.e., adding in a new type of transportation without updating the "factors" sheet will break the formulas)
Hopefully this helps, let me know if you have any questions.