Washington State's Gasoline Price Hit the historical High - PowerBI Dashboard

Washington State's Gasoline Price Hit the Historical High

Recently, I heard the news that the gas price of Washington hit a historical high in June 2023 , even unseated California, though the Gas prices are still lower than the inflation-plagued summer months last year. The average price in King County exceeded $5 a gallon on Jun 21st per The Seattle Times. No wonder when I fill up my gas at Costco, the price is almost 5 dollars per gallon. Normally, I'm not price sensitive, but this time, it surprised me.

So I grabbed the dataset from U.S. Energy Information Administration and work out a Power BI dashboard. 



The first chart which is the Line and clustered Column Chart to show the gas price tendency of Washington State together with other 4 states as California, Texas, New York and Florida. The gas price I'm using is All Grades All Formulations Retail Gasoline Prices(Dollars per Gallon). It looks like California always has the highest gas price in the past 5 years and Washington ranked 2nd place until June 2023. 


I also put the important events during these 5 and half years as columns on the chart.


The 2nd chart is the clustered column chart to show the price change rate on the same month between two consecutive years for the Washington state. For example, compare the price of Jan 2018 and the price of Jan 2019 to see if it increases/decrease rate.


You can see the price has increased a lot from 2020 to 2021, and from 2021 to 2022. The average change rate for the same month of 2020 to 2021 is 29.61%. There are 8 months' change rate above it. The average change rate for the same month of 2021 to 2022 is 33.46%. There are 7 months' change rate above it. The change rate of Jun 2021 to Jun of 2022 is the highest which is 50.94%. This kind reflects that the inflation rate has largest increase in 40 years in Jun 2022.
The process of creating this dashboard is as below:
  1. Import the data files as csv format to the Power BI.
  2. Create a calendar table to add to the data model. A calendar table helps in better data manipulation regarding different date range filtering, date calculations, etc. Here is the DAX code of the calendar table.

Use the CALENDARAUTO( ) function to get all the dates and limit the date range within the date range of City_Monthly_GasPrice table, then generate the columns like Year, Month, Quarter, YearMonth and MonthYear.

  1. Create an event table to be shown as the columns in the Line and clustered Column Chart.

  2. Work out the price change rate on the same month between two consecutive years. 

        

    5.   Create the data model with the relationships.

    6. Select the proper charts to present the data and tell the story. 

Comments

Post a Comment

Popular posts from this blog

Schema and Security Control in SQL server

Image
dbo is always seen as a SQL server table's schema. dbo stands for database owner. The dbo schema is the default schema of every database. When user created with  CREATE USER   T-SQL command have dbo as their default schema. The dbo schema is owned by the dbo user account.  SQL server also provides other pre-defined schemas that have the same names as the built-in db users and roles: db_accessadmin, db_backupoperator, db_datareader, db_datawriter, db_ddladmin, db_denydatareader, db_denydatawriter, db_owner, db_securityadmin . These exist for backward compatibility. The recommendation is to not use them for user objects.  User can drop the schemas that have the same names as the fixed database roles - unless they're already in use, in which case the drop-command will simply return an error and block the drop of the used schema. For example: IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'db_accessadmin') DROP SCHEMA [db_accessadmin] GO If user drops the...
Read more

The statistics of competition results in Excel

Image
Recently, my son participated in a local academic competition alongside other kids of same grade from various schools. During the group contest, held in the same classroom, his team which has 4 kids from same class felt confident as they competed against other school teams. However, to their surprise, their school didn’t secure a spot in the top eight. The team they considered less capable outperformed them. As a data enthusiast mom, I’m eager to delve into the reasons behind this unexpected outcome. I'm going to divide the observants into 4 groups:  Group1: participants from school A, Group2: participants from school B,  Group3: participants from school C, Group4: All the participants. First, I'm going to study the Descriptive Statistics of each group's scores.  Descriptive statistics refer to the analysis, summary, and presentation of findings related to a data set derived from a sample or the entire population. These statistics help us understand and organize char...
Read more