Workshops Microsoft Excel_2.6.pptx para ajuda

Excel

1. Tool 1 Workshops Microsoft Excel Advanced Level : Dashboards Miguel Soares, 2021 Dashboards Tool 2 DAX

2. Tool 1 Workshops Microsoft Excel Dashboards Miguel Soares, 2021 Dashboards  often provide at-a-glance views of key performance indicators (KPIs) relevant to a particular objective or business process.

3. Workshops Microsoft Excel Miguel Soares, 2021 Dashboards includes 1. Tables with support call numbers and costs. 2. A PivotTable that shows actual and budgeted support call costs. 3. A sparkline graph that shows the month-by-month call numbers. 4. Status icons that show if support call numbers exceed budgeted numbers. 5. A line chart that shows actual and budgeted support call numbers. 6. A PivotChart (its underlying data is shown in the PivotTable). 7. A slicer to filter the data in the Pivot table by month. Dashboards Tool 1 Dashboards

4. Workshops Microsoft Excel Miguel Soares, 2021 Planning your dashboard What metrics Which of these metrics are KPIs versus just supporting/contextual information Which views of each metric will be included: a trend (and, if so, its granularity: daily, weekly, monthly, etc.), a total, a comparison to a target or a prior period, etc. Dashboards Tool 1 Dashboards

5. Tool 1 Workshops Microsoft Excel Dashboards Miguel Soares, 2021 Creating your dashboard Using Get & Transform to create queries that connect and combine data sources Keeping the data in your workbook up to date Creating a data model in Excel 2016 Data > Get & Transform Data > Get Data Enable selection of multiple tables Managing your data model and create Relationships between your tables Dashboards

6. Tool 1 Workshops Microsoft Excel Dashboards Miguel Soares, 2021 Creating your dashboard Using measures in Excel 2016 You can then create measures using any of the following approaches: New Measure button on the PowerPivot ribbon , or In the PowerPivot window , in the Calculation Area , by typing a measure name and formula into the formula area of a cell, or From the field list of PivotTable , you can right-click a table name and add a measure . Creating charts/pivot charts in Excel - Conventional charts are best used for aggregated data. Dashboards

7. Tool 1 Workshops Microsoft Excel Dashboards Miguel Soares, 2021 Creating your dashboard Creating a sparkline - can be used to visually represent and show a trend Sharing your dashboard with others Save to a SharePoint site Publish to Power BI Link your dashboard dynamically in a PowerPoint slide deck Dashboards In PowerPoint, on the Insert tab, select Object . In the Insert Object dialog box, select Create from file . Select Browse ,, find the Excel workbook and link to. Select Link , and select OK .

8. Tool 1 Workshops Microsoft Excel Dashboards Miguel Soares, 2021 Create a Student data model with Aggregate data (Average by Class Name) from a Grade (Power Query)

9. Tool 1 Workshops Microsoft Excel Dashboards Miguel Soares, 2021 Create a Dashboard for a call center

10. Tool 2 Workshops Microsoft Excel DAX Miguel Soares, 2021 To analyze critical sales data across several product categories and for different date ranges Fundamental concepts: Syntax, Functions, and Context.

11. Workshops Microsoft Excel Miguel Soares, 2021 Create a simple formula for a calculated column In Excel, on the Power Pivot ribbon, click Power Pivot Window . In the Power Pivot window, click the FactSales table (tab) . Scroll to the right-most column, and then in the column header, click Add Column . Click in the formula bar and insert DAX formula   Data Analysis Expressions Tool 2 DAX

12. Workshops Microsoft Excel Miguel Soares, 2021 Create a measure formula Click in any empty cell in the Calculation Area Example: Previous Quarter Previous Quarter   Sales: =CALCULATE( FactSales [Sales], PREVIOUSQUARTER( DimDate [ DateKey ])) Data Analysis Expressions Tool 2 DAX

13. Workshops Microsoft Excel Miguel Soares, 2021 Create a filter Click in any empty cell in the Calculation Area Example: Previous Quarter Previous Quarter   Sales: =CALCULATE( FactSales [Sales], PREVIOUSQUARTER( DimDate [ DateKey ])) Data Analysis Expressions Tool 2 DAX