Combining our passions with learning can make the experience all the more rewarding. For me, I’ve recently become interested in further developing my skills with Power BI, a Microsoft business analytics tool. At the same time, I’m also an avid homebrewer and passionate about art and graphic design. To make learning Power BI more fulfilling, I decided to link it with my love of homebrewing. By using the data generated during the brewing process to practice visualizing and analyzing data in Power BI, I’ve been able to merge my interests and make learning more enjoyable and meaningful.
In this article, you’ll discover how to transform your own homebrewing data from BeerSmith into an insightful dashboard using Power BI. Displayed below are both static and animated views of the custom-built dashboard I designed to visualize my homebrewing data.
Even with access to the raw data, I was astonished by the trends that emerged as I delved deeper into the analysis. One discovery that caught me off guard was that I had only brewed 31 distinct styles over the years. Sounds like a lot, right? Well as of the 2021 version of the BJCP Guidelines, there are a total of 117 distinct beers that could be brewed. I’ve personally never had a goal to brew every style, but I thought I was at least halfway there. Another trend that emerged was a discouraging decline in the rate of my homebrewing. Nevertheless, by leveraging Power BI, I was able to gain valuable insights that will help me to better understand my homebrewing journey and may even motivate me in other ways.
A Quick Primer on Power BI
Power BI is a data modeling tool that allows you to extract and transform data from various sources and create relationships that help you to analyze and visualize the data. You can use the built-in visualization tools, such as charts, graphs, maps, and tables to create compelling reports and dashboards. With Power BI, you can share your insights by publishing your reports and dashboards to the cloud and sharing them with specific users or groups or making them publicly available.
There is a free version of Power BI available for personal use called Power BI Desktop. It allows you to connect to various data sources and create reports and visualizations. However, if you want to share your reports and dashboards with others online or use additional features such as data refresh or collaboration, you will need to upgrade to a paid version of Power BI. In this article, we’ll be using the free version.
Getting Ready to Build Your Dashboard
To begin creating your homebrewing dashboard, two prerequisites are required: installation of Power BI Desktop on your computer and preparation of your homebrewing data for analysis. The data could come from any homebrewing software, although the article is written with BeerSmith in mind.
Step 1: Install Power BI Desktop
To install Power BI Desktop for free, follow these steps:
Go to the Power BI website.
Click on the Download Free button.
On the next page, select your preferred language and click Download.
Once the download is complete, open the installation file and follow the on-screen instructions to install Power BI Desktop.
After the installation is complete, open Power BI Desktop by double-clicking the shortcut icon on your desktop.
Step 2: Export Your Data from BeerSmith
There are several export formats available, including BeerSmith (.bsmx), HTML, CSV, and XML, providing you with ample flexibility in sharing and utilizing your data. For this exercise, we’ll use the CSV format, and generate it from the Recipe view to ensure we only get the columns we need. While you could export the entire schema using Excel, for educational purposes, I wanted to limit it in this tutorial.
To export your recipes in CSV format, do the following:
In the Recipe view, make sure to customize your columns to include the data you want to extract. In particular, you will need these data fields for this tutorial: - Name - Style - Type - Batch Size - Color - IBUs - Measured ABV - Style Number - Calories
Click File, and then Save Report as CSV.
The data in your CSV file should look something like this:
“Name”,”Style”,”Type”,”Batch Size”,”Date”,”Color”,”IBUs”,”Measured ABV”,”Style Number”,”Calories”,
“American Pale Ale”,”American Pale Ale”,”All Grain”,”5.00",”14 May 2016",” 8.0 SRM”,”39.5",”5.1",”10A”,”150.1",
“American Strong Ale ****”,”American Strong Ale”,”All Grain”,”5.00",”26 May 2019",” 13.2 SRM”,”103.4",”9.2",”22B”,”242.9",
“Apricot Pale Ale ****”,”Fruit Beer”,”All Grain”,”5.00",”01 Jul 2022",” 5.7 SRM”,”37.2",”5.4",”29A”,”180.3",
“Apricot Pale Ale *****”,”Fruit Beer”,”All Grain”,”5.00",”07 Mar 2020",” 5.7 SRM”,”14.0",”5.7",”29A”,”179.4",
“Apricot Wheat Ale”,”American Wheat or Rye Beer”,”Extract”,”5.00",”30 Aug 2013",” 8.1 SRM”,”55.3",”4.8",” 6D”,”167.1",Step 3: Import Your CSV Data into Power BI
To import a BeerSmith CSV file into Power BI Desktop, follow these steps:
Open Power BI Desktop and click on Get Data in the Home ribbon.
Select Text/CSV from the list of available data sources.
In the File window, browse for and select the BeerSmith CSV file you saved in the previous step.
Click Open to import the CSV file.
In the Navigator window, select the table or tables you want to import.
Click Load to import the data into Power BI Desktop.
Power BI Desktop will automatically create a data model based on the imported data, which you can use to create visualizations and reports.
You should now have a view similar to this, providing you with a solid foundation to begin building your personalized dashboard.
Building Your Dashboard
Now we are ready to build! By the end of this article, you will have created a custom dashboard that includes a variety of useful visualizations to help you better understand your homebrewing data. The visual below serves as a reference point for the end goal of the tutorial, which is to create an organized and informative dashboard for your own homebrewing data.
The dashboard will feature a detailed breakdown of the various beer styles you have brewed over your brewing history, including an interactive view of the data with a count of style and style number for BJCP, average calories, and the breakdown of all-grain versus extract brewing methods. Additionally, the dashboard will display a trend analysis of your brewing habits both overall and per individual style. Clearly, I’m an American IPA fan!
Step 1: Add a Pie Chart to Visualize Beer Variety
A pie chart visualization in Power BI can be used in homebrewing data to display the proportion of different beer styles brewed over a period of time. This allows you to easily see which styles are most commonly brewed and how they contribute to the total number of brews. Additionally, a pie chart can be used to show the percentage breakdown of different brewing methods used, such as all-grain versus extract, giving insights into the brewing process.
To add a pie chart:
Select the Pie chart visualization icon from the Visualizations pane. This will add it to your dashboard. Drag it out to take up around 80% of the dashboard space.
In the Data pane, select Style. This will add that data to the Legend field well.
Drag another instance of Style from the Data pane into the Values field well.
You have the freedom to adjust the appearance of the pie chart to your liking by utilizing the formatting options provided in the Visualizations pane. As a result, your dashboard should now resemble this image.
Step 2: Add Cards to Visualize Beer Metrics
A card visualization in Power BI can be used in homebrewing data to display a single metric, such as the total number of brews, the average ABV, or the average IBU of all brews. It can also be used to display other useful data points such as the percentage of all-grain versus extract brewing methods used, or the number of different beer styles brewed. By using a card visualization to display a single data point, you can quickly and easily communicate important information to your audience, allowing them to focus on the most important insights from your data.
To add a card:
In the Visualizations pane, click on the Card icon.
Drag and drop the Style data field onto the card.
In the Visualizations pane, change the Field from First Style to Count.
Repeat the process to add Style Number to the canvas, but use Count (Distinct) for the value.
To add a personal touch to the card’s appearance, you can utilize the formatting options located in the Visualizations pane. Your dashboard should now resemble this, and with any luck, you’ve brewed more beers than I have!
Step 3: Add a Gauge to Visualize Beer Calories
A gauge visualization in Power BI is useful for displaying a single value within a range of values. It is often used to show progress towards a goal or target. For homebrewing data, a gauge visualization could be used to display the alcohol by volume (ABV) percentage of a specific beer, the final gravity readings, or in our example, calories.
To add a gauge:
From the Visualizations pane on the right side of the screen, select the Gauge visualization.
Drag and drop the Calories data field onto the card.
Tweak the gauge properties to your liking, such as the minimum and maximum values, color scheme, and whether or not to display target values. In my own example, I’ve chosen to utilize the average calorie count instead of the total sum — after all, who wants to see all those calories adding up.
Looks like this dashboard is fermenting nicely!
Step 4: Add a Donut Chart to Visualize your Brewing Methods
A donut visualization in Power BI could be used to display the proportion of a specific ingredient used in a particular recipe or across all recipes. For example, you could create a donut chart to show the percentage of the total amount of hops used in all your recipes, or to show the ratio of different types of malt used in a particular recipe. For the purposes of this example, I am going to be using this chart to visualize the breakdown of my all-grain beers versus extract beers.
To add a donut chart:
Select the Donut chart visualization icon from the Visualizations pane. This will add it to your dashboard.
In the Data pane, select Type. This will add that data to the Legend field well.
Drag another instance of Type from the Data pane into the Values field well.
Remember, when it comes to designing your dashboard, you’re the brewmaster. So don’t be afraid to experiment and add your own personal touch to the charts. Just like brewing beer, creating a dashboard is both an art and a science — and the results can be just as delicious!
Step 5: Add a Line Chart to Visualize your Brewing Trends Over Time
Our final addition to the dashboard is a line chart. A line chart visualization in Power BI can be useful for showing trends over time in homebrewing data. For example, a line chart could be used to track the number of batches brewed each year, or to show how a particular brewing metric has changed over time. The x-axis would typically represent time (e.g., years, months, or days), and the y-axis would show the values of the metric being tracked. In this example, we are going to track the number of gallons brewed over time.
To add a line chart with a trend line:
Click on the Line chart icon from the Visualizations pane on the right-hand side of the screen.
In the Data pane, drag the Date data field to the X-axis. Remove the Quarter, Month, and Day values so you only have Year.
In the Data pane, drag the Batch Size data field to the Y-axis.
To add a trend line to your line chart, click on the line chart visualization to select it and then go to the Visualizations pane on the right-hand side of the screen.
Click the Analytics button and then and click on the Trend line button to enable it. You can customize the trend line by changing its color and style, adjusting its forecast period and confidence interval, and more.
And that wraps up our last visualization for your homebrewing dashboard!
Conclusion
I hope you’ve seen how Power BI can be an incredible tool for homebrewers who want to delve deeper into their brewing data. With Power BI, you can quickly and easily analyze trends and patterns in your brewing data and use that information to make better decisions about your brewing process. And if you decide to continue with this dashboard or start over with another data set, the possibilities for customization are limitless.
🍻Cheers to visualizing your way to brewing perfection with Power BI!