“Eclectic Coffee Company” - Power BI

Data Visualizations

Data Visualization is a visual (pictorial) representation of data. Data may be hard to analyze in its raw format. Therefore, data visualization can bring meaning to the data to allow decision makers to be able recognize patterns, outliers, trends, etc. that may not have been noticed before. PowerBI is a data analytics program that creates data visualizations out of raw data. PowerBI has over 20 different types of visualizations and even allows users to create their own types of visualizations. Each visualization tells a different story with the data. Data can be presented one-dimensional in a Pie Chart visualization which is a circular graph used to determine proportion sizes. Data can also be displayed in ordered groups, such as a Tree map. A Tree map shows data in a hierarchical flow. Or data can be presented in multiple dimensions to display 2 or more features of the data such as a Stacked Bar Graph. Different visualizations can be used by decision makers to understand the data and answer particular questions. This case will have you create different types of visualizations based on the question you are looking for. That question may be what is the pay distribution by race or gender? Or what is the job title distribution among race and gender? Or you may want to know if the company current have diversity in race among their employees. Visualizations within PowerBI will answer all of these questions.

PowerBI Software Download Instructions

1. Go to the website: https://powerbi.microsoft.com/en-us/desktop/

2. Click on “Download free”

You can also go to “See download or language options” to see the versions for your desktop. Power BI currently only work for Windows systems. There is no Mac version yet.

3. Then it will lead you to the Microsoft App Store. Click on Open Microsoft Store:

4. Click on “Install”:

5. Sometimes it will ask you to login, but you don’t have to, just close the window and download the app and hit “launch” when done:

6. You can pin the app to the task bar. When it opens, you can again login with your Microsoft account, but you can also just close the window and you are ready to explore Power BI:

Power BI “Building a Dashboard” Instructions

Start with downloading all files in the “Summary of Findings – ECC Engagement” folder on Blackboard to your computer:

· Audit Firm Logo

· Payroll Data of Eclectic Coffee Company

Now that you have downloaded the files, Open Power BI to start your engagement.

Within Power BI, click on “Get Data” in the top menu bar and then select “Excel.” Select the data file you have saved to your computer named “Payroll Data of Eclectic Coffee Company.”

The data will import and you will need to click “Payroll Data” to select the data appropriate data.

Once you see the data in the preview pane and you will then select “Load”

Now the data will be in Power BI and you are able to see the data when you select this image in the left hand side menu bar.

Now you begin to build the HR Dashboard. This dashboard will create visualizations of the company’s payroll data and allow your firm to complete the agreed upon procedures. This visualization will be part of the Summary of Findings that you provide to the Eclectic Coffee Company.

First select this report image in the left hand menu bar, you will always need to work in this part of the dashboard.

*A completed Dashboard is shown on the last page of these instructions for reference*

Visualization 1: Analyze Job Title by Gender

You are going to build a visualization using their data to determine the distribution of job title across gender. Next you will select the visualization you choose to present the data in. This set of instructions will show you how to create a “Clustered Column Chart.” You may choose another type of visualization type. Under the VISUALIZATION menu (on the right hand side) this is depicted by the following image. When you select this image it will appear on your dashboard. You are able to move the chart and the change the size of it. Now you will need to enter in the proper fields into the table to create your visualization. When the table is selected you will drag and drop items from your FIELDS (on the right hand side) menu into the VISUALIZATION menu. Before dragging and dropping the field items into the VISUALIZATION menu please select in the VISUALIZATION menu bar. Next you will create the following table using the following fields.

You will put your cursor over JOB TITLE in the FIELDS menu, then drag and drop the items over to the AXIS subheading. You will repeat the same step with GENDER.

Next you will put your cursor over GENDER in the FIELDS menu, then drag and drop the item and place it under the Legend subheading.

Lastly, you will place your cursor over GENDER in the FIELDS menu, then drag and drop the item and place it under the Value subheading. You will want to select the down arrow, and select “Show value as > Percent of grand total”

You will now have a table showing Job Title by Gender. Your table will look something like this (job titles may be in different order and you have not edited the table yet to add a title and percentages – this will be done as the last step):

Visualization 2: Analyze Job Title by Race

AFTER EVERY VISULIZATION THAT YOU BUILD, MAKE SURE TO CLICK OUTSIDE OF THE BAR CHART IN THE WHITE SPACE TO CREATE A NEW TABLE!!

You are going to build a visualization using their data to determine the distribution of job title across race. Next you will select the visualization you choose to present the data in. This set of instructions will show you how to create a “Clustered Column Chart.” You may choose another type of visualization type. Under the VISUALIZATION menu this is depicted by the following image. When you select this image it will appear on your dashboard. You are able to move the chart and the change the size of it. Now you will need to enter in the proper fields into the table to create your visualization. When the table is selected you will drag and drop items from your FIELDS menu into the VISUALIZATION menu. Before dragging and dropping the field items into the VISUALIZATION menu please select in the VISUALIZATION menu bar. Next you will create the following table using the following fields.

You will put your cursor over JOB TITLE in the FIELDS menu, then drag and drop the item over to the AXIS subheading.

Next you will put your cursor over RACE in the FIELDS menu, and then drag and drop the item and place it under the Legend subheading.

Lastly, you will place your cursor over RACE in the FIELDS menu, then drag and drop the item and place it under the Value subheading. You may choose to show the data as either a COUNT of race per position or a percentage of race per position (“Show value as > Percent of grand total”)

You will now have a table showing Job Title by Race. Your table will look something like this (job titles may be in different order and you have not edited the table yet to add a title and percentages – this will be done as the last step:

Visualization 3 & 4: Analyze Salary (Part Time and Full Time) by Gender

AFTER EVERY VISULIZATION THAT YOU BUILD, MAKE SURE TO CLICK OUTSIDE OF THE BAR CHART IN THE WHITE SPACE TO CREATE A NEW TABLE!!

You are going to build two visualizations using their data to determine the distribution of salary across gender. The data for salary is given as hourly for part-time workers and hourly for full-time workers. That is why you will develop two charts to separate that data. Next you will select the visualization you choose to present the data in. This set of instructions will show you how to create a “Stacked Column Chart.” You may choose another type of visualization type. Under the VISUALIZATION menu this is depicted by the following image. When you select this image it will appear on your dashboard. You are able to move the chart and the change the size of it. Now you will need to enter in the proper fields into the table to create your visualization. When the table is selected you will drag and drop the items from your FIELDS menu into the VISUALIZATION menu. Before dragging and dropping the field items into the VISUALIZATION menu please select in the VISUALIZATION menu bar. Next you will create the following table using the following fields.

First, create the bar chart for PART TIME WORKERS:

You will put your cursor over GENDER in the FIELDS menu, then drag and drop the item and place it under the AXIS subheading.

Next you will put your cursor over GENDER in the FIELDS menu, and then drag and drop the item and place it under the Legend subheading.

Next you will put your cursor over REG PAY in the FIELDS menu and then drag and drop the item and place it under the Value subheading. You will need to click the down arrow next to REG Pay and select “Average” in the menu.

You will scroll down the menu and see the FILTERS menu. You will put your cursor over FT/PT in the FIELDS menu and then drag and drop the item under the Visual level filters.

Once you have entered FT/PT into the filters you will select the down arrow, and then select only the Part Time Field.

Second, create the bar chart for FULL TIME WORKERS: You will complete the same exact steps noted on the page above. The only difference will be within your filter . Once you have created the chart and applied the filter, you will select Full Time rather than part time.

Your tables will look something like this (you have not edited the table yet to add a title – this will be done as the last step):

Visualization 5 & 6: Analyze Salary (Part Time and Full Time) by Race

AFTER EVERY VISULIZATION THAT YOU BUILD, MAKE SURE TO CLICK OUTSIDE OF THE BAR CHART IN THE WHITE SPACE TO CREATE A NEW TABLE!!

You are going to build two visualizations using their data to determine the distribution of salary across race. The data for salary is given as hourly for part-time workers and hourly for full-time workers. That is why you will develop two charts to separate that data. Next you will select the visualization you choose to present the data in. This set of instructions will show you how to create a “Stacked Column Chart.” You may choose another type of visualization type. Under the VISUALIZATION menu this is depicted by the following image. When you select this image it will appear on your dashboard. You are able to move the chart and the change the size of it. Now you will need to enter in the proper fields into the table to create your visualization. When the table is selected you will drag and drop items from your FIELDS menu into the VISUALIZATION menu. Before dragging and dropping the field items into the VISUALIZATION menu please select in the VISUALIZATION menu bar. Next you will create the following table using the following fields.

First, create the bar chart for PART TIME WORKERS:

You will put your cursor over RACE in the FIELDS menu, then drag and drop the item and place it under the AXIS subheading.

Next you will put your cursor over RACE in the FIELDS menu, and then drag and drop the item and place it under the Legend subheading.

Next you will put your cursor over REG PAY in the FIELDS menu and then drag and drop the item and place it under the Value subheading. You will need to click the down arrow next to REG Pay and select “Average” in the menu.

You will scroll down the menu and see the FILTERS menu. You will put your cursor over FT/PT in the FIELDS menu and then drag and drop the item under the Visual level filters.

Once you have entered FT/PT into the filters you will select the down arrow, and then select only the Part Time Field.

Second, create the bar chart for FULL TIME WORKERS: You will complete the same exact steps noted on the page above. The only difference will be within your filter . Once you have created the chart and applied the filter, you will select Full Time rather than part time.

Your tables will look something like this (you have not edited the table yet to add a title – this will be done as the last step):

Visualization 7: Racial Diversity for the Company as a whole

AFTER EVERY VISULIZATION THAT YOU BUILD, MAKE SURE TO CLICK OUTSIDE OF THE BAR CHART IN THE WHITE SPACE TO CREATE A NEW TABLE!!

You may need to move a new page by selecting the + sign at the bottom of the dashboard

You are going to build a visualization using their data to determine the distribution of race within the company. Next you will select the visualization you choose to present the data in. This set of instructions will show you how to create a “Pie Chart.” You may choose another type of visualization type. Under the VISUALIZATION menu this is depicted by the following image. When you select this image it will appear on your dashboard. You are able to move the chart and the change the size of it. Now you will need to enter in the proper fields into the table to create your visualization. When the table is selected you will drag and drop items from your FIELDS menu into the VISUALIZATION menu. Before dragging and dropping the field items into the VISUALIZATION menu please select in the VISUALIZATION menu bar. Next you will create the following table using the following fields.

You will put your cursor over RACE in the FIELDS menu, then drag and drop the item over to the Legend subheading.

Lastly, you will place your cursor over RACE in the FIELDS menu, then drag and drop the item and place it under the Value subheading. You may choose to show the data as either a COUNT of race per position or a percentage of race per position (“Show value as > Percent of grand total”)

You will now have a table showing racial diversity of the company. Your table will look something like this (race titles may be in different order as you have not edited the table yet to add a title and percentages – this will be done as the last step:

Visualization 8: Racial Diversity for the Company as a whole

AFTER EVERY VISULIZATION THAT YOU BUILD, MAKE SURE TO CLICK OUTSIDE OF THE BAR CHART IN THE WHITE SPACE TO CREATE A NEW TABLE!!

You may need to move a new page by selecting the + sign at the bottom of the dashboard

You are going to build a visualization using their data to analyze two separate variables. You will create a visualization to analyze the distribution of pay among race. Next you will select the visualization you choose to present the data in. This set of instructions will show you how to create a “Tree map.” You may choose another type of visualization type. Under the VISUALIZATION menu this is depicted by the following image. When you select this image it will appear on your dashboard. You are able to move the chart and the change the size of it. Now you will need to enter in the proper fields into the table to create your visualization. When the table is selected you will drag and drop items from your FIELDS menu into the VISUALIZATION menu. Before dragging and dropping the field items into the VISUALIZATION menu please select in the VISUALIZATION menu bar. Next you will create the following table using the following fields.

You will put your cursor over RACE in the FIELDS menu, then drag and drop the item over to the Group subheading.

Next you will put your cursor over REG PAY in the FIELDS menu, and then drag and drop the item and place it under the Details subheading.

Lastly, you will place your cursor over RACE in the FIELDS menu, then drag and drop the item and place it under the Value subheading. You may choose to show the data as either a COUNT of race per position or a percentage of race per position (“Show value as > Count of RACE”)

You will scroll down the menu and see the FILTERS menu. You will put your cursor over FT/PT in the FIELDS menu and then drag and drop the item under the Visual level filters.

Once you have entered FT/PT into the filters you will select the down arrow, and then select the Part Time or Full Time Field.

You will now have a table showing distribution of pay among race of the company. Your table will look something like this (race titles may be in different order as you have not edited the table yet). You will notice that RACE is shown by the color blocks and the size of them is determined by the P/T Pay and the count of employees who receive that pay. For example, 2 White employees make $18/hour which is shown in the top left blue box.

EDITING VISULIZATIONS:

STEP 1: Create a Title for your Summary of Findings

In the top menu bar under “Home” select “Text Box” . A text box will appear where you can enter the title of the Report. Once you enter the Title you can change the font, size, position, etc. You will want to move this box to either the top or bottom or you dashboard so that you will have room for your visualizations. An example title may be “Eclectic Coffee Company Findings” or “Human Resources: Inclusion & Diversity.”

STEP 2: Paste the Audit Firm Logo on the Dashboard

In the top menu bar under “Home” select “Image” . You have downloaded the logo to your computer. When you select “Image” you will be prompted to select the file you have saved named “Audit Firm Logo.” Once you insert the image, you can change the size and placement of the image on your dashboard.

STEP 3: Make this a true audit report by cleaning up the graphs

Now that you have created all of your visualizations, you can clean up the bar charts and make it appear cleaner by editing it within the VISUALIZATION menu . Remember you are giving this exhibit to the client so you want it to read easily. Each table you want to edit, first CLICK ON THE BAR CHART, then under the VISUALIZATION menu click . Within this menu you can edit the Title under the Title menu (change the title, font, color, placement, etc.). You can add Data Labels showing the percentage of each category by selecting Data Labels to ON. You can change the placement of the Legend (ex. Race categories) under the Legend menu and changing the position. You can also change the position of each bar chart and the size of it as well. The amount of editing you can do is endless.

Once you have completed your editing and placement of visualizations on the dashboard, you are finished. You must save it as a PDF (Go to File > Export to PDF) to submit it as an exhibit to your Summary of Findings. Once a PDF is created save it and submit it with your Summary of Findings.

Here is an example of Page 1 of a final Dashboard/exhibit to your Summary of Findings