Final Power BI

University:
California State University, Northridge
Course:
FIN 430 | International Financial Management
Academic year:

2024
Views:

0

Pages:

72
Author:

Aditya Prakash

PRACTICAL 01 a. PivotTables & Power Query in Power BI Q1. Create the PivotTable and apply the necessary filters to display the top 3 regions with the highest sales, broken down by product. Steps: Open Power BI Desktop. Import your data sources (Sales and Customers tables). Go to the Report view. From the Visualizations pane, select the Matrix visual. In the Fields pane: Drag "Region" from Customers to Rows Drag "ProductName" from Products to Columns Drag "Total Sales " from Sales to Values In the Filters pane, add "Region" as a Top N filter: Set "Show items" to "Top" Set " By value" to "Sum of Total Sales " Set "Number of items" to 3 Q2. Use Power Query to group the data by date, and create a new column that shows the total sales for each date. Steps: In Power BI Desktop, go to Home > Transform data to open Power Query Editor. Select the Sales query. Right-click on the Date column, choose Group By. In the Group By dialog: Group by: Date New column name: Total Sales Operation: Sum Column: Sales Amount Click OK to apply the grouping. Close & Apply to load the changes back to Power BI. b. Data Cleansing in Power Query Q1. Filter out, using Power Query, those rows with the missing "Customer ID" and fill in the missing values in the "Order Amount" column with the average order amount. Steps: In Power BI Desktop, go to Home > Transform data to open Power Query Editor. Select the Orders query. Select CustomerID column, drop down menu > Uncheck the Option that says “Null” Clic k “OK” Select the OrderAmount column. Go to T ransform > Statistics Group > Average Note the average value Select the OrderAmount column again > Transform > Replace Value In the Replace Values dialog: Value to find: null Replace with: Noted average value Click OK to apply the changes. Close & Apply to load the changes back to Power BI. Q2. Use Power Query to transform and standardize the "Country" column to make its entries uniform in format, say, "USA". Steps: In Power BI Desktop, go to Home > Transform data to open Power Query Editor. Select the Customers query. Select the Country column. Go to Transform > Format > Trim. Go to Transform > Format > Uppercase. Go to Add Column > Custom Column. In the Custom Column dialog: New column name: Standardized Country Custom column formula: if [Country] = "UNITED STATES" or [Country] = "US" or [Country] = "U.S.A." then "USA" else [Country] PRACTICAL 02 a. Relationship Diagram tool Q1. Use the Relationship Diagram tool to add a relationship between the "Orders" and "Customers" tables, using the "Customer ID" field. Test this by showing you can now perform an analysis of orders by customer name. Steps: In Power BI Desktop, go to the Model view. Locate the "Orders" and "Customers" tables in the diagram. Click and drag the " CustomerID " field from the "Orders" table to the " CustomerID " field in the "Customers" table. In the Create Relationship dialog: Confirm that Cardinal ity is set to "One to Many " Ensure Cross filter direction is set to "Single" Check "Make this relationship active" Click Save to create the relationship. Switch to Report view. Create a new visual (e.g., a Table or Matrix) with the following fields: From Customers table: CustomerName From Orders table: OrderAmount If the visual shows data correctly, the relationship is working. Q2. Add a relationship between the "Sales" and "Products" tables, using "Product ID," then use this relationship to add a report showing total sales amounts by product category. Steps: In Model view, drag the " ProductID " field from the "Sales" table to the " ProductID " field in the "Products" table. In the Create Relationship dialog: Set Cardinality to "Many to Many " Set C ross filter direction to "Both " Check "Make this relationship active" Click Save to create the relationship. Switch to Report view. Create a new visual (e.g., a Bar chart) with: Axis: Products[Category] Values: Sales[ TotalAmount ] The chart should now show total sales by product category. b. Calculated Columns & Measures Q1. Create a calculated column in Power BI that multiplies "Quantity" by "Price per Unit" to produce a "Total Revenue" column. Steps: In Model view, select the Sales table. Go t o Calculations > New Column. In the formula bar, enter: Total Revenue = Sales[Quantity] * Sales[Price per Unit] Press Enter to create the column. Format the new column as Currency if needed. Q2. Create a measure in Power BI that calculates the average of the "Sales Amount" column, and use this measure in a visual to display the average sales amount. Steps: In Report view, select the Sales table in the Fields pane. Go to Calculations > New Measure. In the formula bar, enter: Average Sales Amount = AVERAGE( Sales [Sales amount]) Press Enter to create the measure. Create a new Card visual. Drag the new "Average Sales Amount" measure into the Card visual. c. Dates in Power BI Q1. Use Power BI's date functions to develop a report that will summarize total sales by month for the current year and plot this in a line or bar chart. Steps: First, ensure you have a Date table. In the Data view, select the table containing your Date column Go to Modeling > New Table Enter the following DAX expression: Date = CALENDAR(MIN(Sales[Date]), MAX(Sales[Date])) Create a measure for current year sales: Current Year Sales = CALCULATE(SUM(Sales[Total Revenue]), YEAR(Date[Date]) = YEAR(TODAY())) Create a line chart: X-axis: Date[ MonthName ] Y-axis: Current Year Sales measure Add a filter to the visual for Date[Year] = YEAR(TODAY()) Q2. Use Power BI to build a measure that identifies what the percentage difference is between the total sales this year compared to last year and plot that in a visual. Steps: Create measures for this year and last year sales: This Year Sales = CALCULATE( SUM(Sales[ TotalAmount ]), YEAR(Date[Date]) = YEAR(TODAY()) ) Last Year Sales = CALCULATE( SUM(Sales[ TotalAmount ]), YEAR(Date[Date]) = YEAR(TODAY()) - 1 ) Create a measure for the percentage difference: YoY Sales % Difference = DIVIDE( [This Year Sales] - [Last Year Sales], [Last Year Sales] ) Create a Card visual and add the YoY Sales % Difference measure. Format the visual to display as a percentage. PRACTICAL 03 a. Dashboard Design Q1. Design a dashboard with key metrics of total sales, sales by region, top-selling products, and year-over-year growth. Steps: Create necessary measures Create a new report page for the dashboard. Add a Card visual for Total Sales. Add a Map visual for Sales by Region: Location: Customers[Region] Bubble size: Sales[ TotalAmount ] Add a Top N filtered Bar chart for Top-selling Products: Y-axis: Products[ProductName] X-axis: Sales[ TotalAmount ] Top N filter: Show top 5 by TotalAmount Arrange visuals in a logical layout, ensuring proper sizing and alignment. Add a title to the dashboard using a Text box visual. Q2. Create a dashboard displaying the number of tasks completed, number of hours worked, and productivity by department. Steps: Assuming we have a Tasks table Create a new report page for the dashboard. Add Card visuals for Tasks Completed and Total Hours Worked. Add a Pie chart for Tasks Completed by Department: Legend: Tasks[Department] Values: Tasks Completed measure Add a Column chart for Productivity by Department: X-axis: Tasks[Department] Y-axis: Productivity measure Add a Table showing detailed breakdown: Rows: Tasks[Department] Values: Tasks Completed, Total Hours Worked, Productivity measures Arrange visuals logically, ensure proper sizing and alignment. b. Slicers and report filters in Power BI Q1. Add slicers to the report for "Region" and "Product Category," allowing users to filter the visualizations dynamically based on their selections. Steps: From the Visualizations pane, add a Slicer visual to your report. Drag Customers[ Region] into the slicer's Field well. Format the slicer: Choose between List or Dropdown style Enable multi-select if desired Add another Slicer visual for Products[ Category]. Format this slicer similarly to the Region slicer. Position the slicers prominently on your report, typically at the top or side. Test the slicers to ensure they filter all relevant visuals on the page. Q2. Create a slicer in Power BI that allows users to filter the report by selecting one or more months, ensuring that all visualizations update accordingly based on the selected time frame. Steps: Add a new Slicer visual to your report. Drag Date[ MonthName ] into the slicer's Field well. In the slicer's Format pane: Set the slicer type to "List" Enable "Multi-select" Optional: Enable "Select All" option Position the slicer prominently on your report. Test the slicer to ensure it filters all date-related visuals on the page. Optional: Add a visual to show which months are currently selected. c. Choosing Maps and Chart according to data Q1. Create and deploy a map visualization, using Power BI to represent sales distribution across cities. Steps: From the Visualizations pane, add a Map visual to your report. Configure the Map fields: Location: Customers[ City] Bubble size: Sum of sales Tooltip: Add relevant fields like Customers[ CustomerName ], Products[ProductName] In lude a title for the map: "Sales Distribution by Country ".Add any necessary filters, such as date range or product category. Q2. Choose an appropriate chart type to reflect both month-wise sales trends and quarterly performance on the same visual. Steps: Create a new Combo Chart (Line and Stacked Column) visual. Configure the chart fields: Shared Axis: Date[Date] (ensure it's at Month level) C olumn values: Sum of sales (This will represent monthly sales) Line values: Quarterly Sales In the Format pane: Set different colors for monthly and quarterly data Add a title: "Monthly Sales Trend with Quarterly Performance". Optional: Add a date slicer to allow users to select specific time periods. PRACTICAL 04 a. Bookmarks & Drill Down Q1. Create bookmarks in Power BI that capture the high-level summary and detailed breakdown views. Then, using a button that toggles between those two views based on those bookmarks within Power BI. Steps: Create two report pages: one for the high-level summary and another for the detailed breakdown. Design your high-level summary page with key metrics and visualizations. Create bookmarks: Go to View > Bookmarks pane to open the Bookmarks pane. On the summary page, click "Add" in the Bookmarks pane to create a "Summary View" bookmark. Switch to the detailed page and create a "Detailed View" bookmark. Create a toggle button: On each page, insert a button (Insert > Buttons > Blank). In the Format pane for the button, under Action: Set Type to "Bookmark" Set Bookmark to the opposite view (e.g., "Detailed View" on the summary page) Style your buttons appropriately and add text like "View Details" or "Back to Summary". Q2. Set up a visual in Power BI that has a drill-down capability to allow users to click a year to view quarterly data, then can drill down into monthly data. Steps: Ensure you have a Date table with Year, Quarter, and Month columns. Create a new Matrix visual. Configure the Matrix fields: Rows: Add Date[Year], Date[Quarter], Date[Month] in this order Values: Add your measure (e.g., Total Sales) In the Visualizations pane, under the Fields section: Click the dropdown next to Date[Quarter] and select "Show next level" Click the dropdown next to Date[Month] and select "Show next level" Format the visual: In the Format pane, under Row headers, turn on "Stepped layout" Enable drill-down options: In the top-left corner of the visual, click the double arrow icon to show the drill-down options Test the drill-down: Start at the Year level Click on a year to drill down to quarters Click on a quarter to drill down to months Use the drill-up arrow in the top-left corner to move back up the hierarchy b. Excel Linkages Q1. Link the Power BI report to the Excel file stored on OneDrive, ensuring the data connection is dynamic so that refreshes in Excel feed automatically into the Power BI report. Steps: Ensure your Excel file is saved on OneDrive. In Power BI Desktop, go to Home > Get Data > More... Select "OneDrive for Business" and click Connect. Navigate to and select your Excel file, then click Open. In the Navigator window, select the tables/sheets you want to import and click Load. Q2. Link your Excel spreadsheet within Power BI and import the data into your model, showing how you are able to create a visual which amalgamates data from the Excel sheet with any other data sources within Power BI. Steps: Import your Excel data: Go to Home > Get Data > Excel Navigate to your Excel file and select it Choose the relevant tables/sheets and click Load Import other data sources as needed (e.g., a database table) Create relationships: Go to Model view Drag fields to connect the Excel data with other sources (e.g., connecting on a common ID field) Create a visual that combines data: Go to Report view Create a new visual (e.g., a Matrix or Table) Add fields from your Excel data to the visua l Format the visual as needed to clearly show the amalgamated data Add a title to your visual explaining the combined data sources c. Power BI Service Sharing Q1. Publish the report to the Power BI service, then share with your colleagues. Set the right permissions to allow them to view and interact with the report to demonstrate how to share. Steps: In Power BI Desktop, go to Home > Publish Select a destination workspace and click "Select" In Power BI Service, navigate to the report Click the "Share" button in the top-right corner In the sharing pane: Enter the email addresses of your colleagues Choose whether to allow recipients to share the report . Now click Share. For more granular control: Go to the workspace containing the report Click the "..." next to the report name Select "Manage permissions" Add users or gr o ups and set their access level (View, Edit, etc.) Q2. Establish Row-level security in Power BI using user-based roles. This will ensure that when it's shared on the Power BI service, users see only the data they are authorized to see. Share the report with RLS applied. Steps: In Power BI Desktop, go to Modeling > Manage Roles Click "Create" to make a new role, name it appropriately (e.g., " SalesTeamUSA ") Select the table to apply RLS (e.g., Sales) In the Table filter DAX expression box, enter a filter expression: [Country] = "USA" . Click Save. Publish the report to Power BI Service In Power BI Service, go to the workspace with your report Click the "..." next to the dataset name and select "Security" Add members to the role: Click "Add member" Enter email addresses for users who should see only USA data Click "Add" and then "Save" Share the Report with Row-Level Security Applied Share the Report: Once RLS is set up, share the report with users. Go to the report, click on the Share button, and enter the email addresses of the users or groups with whom you want to share the report. When users with assigned roles access the report, they will only see the data allowed by their role. PRACTICAL 05 a. Identify and Define KPIs Q1. Identify three relevant KPIs that might go into a sales dashboard. Example: total sales, sales growth %, customer retention rate. Using Power BI, define these KPIs by specifying appropriate measures or calculated fields. Steps: Open Power BI Desktop and load your sales data Create measures for each KPI: a. Total Sales : Total Sales = SUM(Sales[ SalesAmount ]) b. Sales Growth %: - Sales Growth % = VAR CurrentPeriodSales = [Total Sales] VAR PreviousPeriodSales = CALCULATE([Total Sales], DATEADD(Date[Date], -1, YEAR)) RETURN DIVIDE(CurrentPeriodSales - PreviousPeriodSales, PreviousPeriodSales) c. Customer Retention Rate: Customer Retention Rate = VAR CustomersLastYear = CALCULATE(DISTINCTCOUNT(Sales[CustomerID]), DATEADD(Date[Date], -1, YEAR)) VAR RetainedCustomers = CALCULATE( DISTINCTCOUNT(Sales[CustomerID]), INTERSECT( FILTER(ALL(Sales), Sales[Date] >= DATE(YEAR(TODAY())-1, 1, 1) && Sales[Date] < DATE(YEAR(TODAY()), 1, 1)), FILTER(ALL(Sales), Sales[Date] >= DATE(YEAR(TODAY()), 1, 1)))) RETURN DIVIDE(RetainedCustomers, CustomersLastYear) Create a KPI visual for each measure: From the Visualizations pane, select the KPI visual For each KPI, set the following: Indicator: The corresponding measure Trend axis: Date[Date] Target goals: Set as appropriate for your business Format each KPI visual to clearly display the metric and its trend Q2. Chosen and defined KPIs in Power BI: CTR, conversion rate, CPA. Apply KPIs in a visual manner so as to have the performance of the campaign real-time. Steps: Assuming you have a table with campaign data, create measures for each KPI: a. Click-Through Rate (CTR): CTR = DIVIDE(SUM(Campaigns[Clicks]), SUM(Campaigns[Impressions])) b. Conversion Rate: Conversion Rate = DIVIDE(SUM(Campaigns[Conversions]), SUM(Campaigns[Clicks])) c. Cost Per Acquisition (CPA): CPA = DIVIDE(SUM(Campaigns[Cost]), SUM(Campaigns[Conversions])) Create a dashboard page for these KPIs Add a KPI visual for each measure: From the Visualizations pane, select the KPI visual For each KPI, set: Indicator: The corresponding measure Trend axis: Campaigns[Date] Target goals: Set as appropriate for your campaign goals Add a line chart showing the trend of these KPIs over time: X-axis: Campaigns[Date] Y-axis: Add all three KPI measures Add a slicer for Campaign Name to allow filtering by specific campaigns Optional: Add a real-time data connection if your data source supports it: In Power BI Desktop, go to Home > Transform data In Power Query Editor, right-click your query and select "Data source settings" Set up an appropriate refresh schedule or real-time connection based on your data source b. Create a Dashboard using appropriate Visualizations Q1. Create a financial dashboard in Power BI using visualizations such as bar charts for revenue and expenses, and a gauge chart for profit margins. Arrange these visuals to create a cohesive and easy-to-understand layout. Steps: Prepare your data and create necessary measures: Total Revenue = SUM(Financials[Revenue]) Total Expenses = SUM(Financials[Expenses]) Profit Margin % = DIVIDE([Total Revenue] - [Total Expenses], [Total Revenue]) Create a new dashboard page Add a bar chart for Revenue: X-axis: Date[Month] Y-axis: Total Revenue measure Add a bar chart for Expenses: X-axis: Date[Month] Y-axis: Total Expenses measure Add a gauge chart for Profit Margin: Value: Profit Margin % measure Minimum: 0 Maximum: 1 Target: Set based on your business goals (e.g., 0.2 for 20%) Add card visuals for total revenue, total expenses, and net profit Add a matrix visual showing detailed breakdown by department or category Arrange visuals in a logical layout: Place KPI cards at the top Position revenue and expense charts side by side Place the profit margin gauge prominently Put the detailed matrix at the bottom Add a title to the dashboard using a text box Add slicers for date range and any other relevant filters Apply a consistent color theme across all visuals Q2. Design an HR dashboard in Power BI with visualizations such as a pie chart for employee demographics, a line chart for turnover rate, and a KPI visual for average time to hire. Ensure that the dashboard is user-friendly and highlights key insights. Steps: Prepare your HR data and create necessary measures: Total Employees = COUNTROWS(Employees) Turnover Rate = DIVIDE(COUNTROWS(FILTER(Employees, Employees[TerminationDate] <> BLANK())), [Total Employees]) Avg Time to Hire = AVERAGEX(Hiring, Hiring[DaysToHire]) Create a new dashboard page Add a pie chart for Employee Demographics: Legend: Employees[Department] or Employees[ JobLevel ] Values: Count of Employees[ EmployeeID ] Add a line chart for Turnover Rate: X-axis: Date[Month] Y-axis: Turnover Rate measure Arrange visuals in a user-friendly layout: Place KPI cards and critical metrics at the top Ensure a logical flow of information c. Use conditional Formatting Q1. In Power BI, perform the conditioning whereby regions where monthly sales figures are below threshold target should be colored in red to aid in easy identification of areas that require attention. Steps: Create a measure for the sales target: go to the respective Numerical Value and Right Click and get into Conditional Formatting. Create a table or matrix visual with: Rows: Region Columns: Date[Month] Values: Total Sales measure Select the visual and go to the Format pane Expand the Conditional formatting section In the Background color dialog: Based on field: Total Sales Summary: Values Color scale: Minimum: Red Maximum: Green Minimum: Customer and Set your Number Maximum: Custom and set your number Click OK to apply the formatting Q2. Apply conditional formatting in Power BI such that profit margins automatically color green for positives and red for negatives to make the financial performance stand out easily to the business user. Steps: Ensure you have a measure for Profit Margin: Profit Margin = DIVIDE(SUM(Financials[Revenue]) - SUM(Financials[Costs]), SUM(Financials[Revenue])) Create a table or matrix visual with: Rows: Product or Category Columns: Date[Month] Values: Profit Margin measure S e lect the visual and go to the Format pane Expand the Conditional formatting section In the Font color dialog: What field should we base this on? Profit Margin Summarize field by: Values Value: Custom and set your limit Color : Red Value: Customer and set your limit Color : Green Click OK to apply the formatting Powered by TCPDF (www.tcpdf.org)