Power BI - Revenue - Industry Agnostic Revenue - Analysis - Step-by-Step Guide PDF

Preview

Summary

APM...

Description

Instructions to Build: Revenue - Industry Agnostic Revenue - Analysis

1

Important Data Request Form and Power BI Information

2

Data Preparation

3

Import Data into Power BI

4

Reconcile Imported Data to Source Data

5

Tableau vs Power BI Differences

Look for the

i

con throughout this guide for helpful information about Power BI.

Power BI 1. Important Data Request Form and Power BI Information This dashboard is designed to have two periods of data.

Data Request Form Please edit the following sheets only (green tab color) from PowerBI –Revenue - Industry Agnostic Revenue- Analysis - Data Request Form with relevant information: 1. Request: The information entered within this sheet is linked to other sheets in the workbook that are utilized in the Power BI dashboard. It is important that only the cells highlighted in green are edited. Modifying other cells or not entering the correct information within the green cells may result in inaccurate or broken visualizations in the Power BI dashboard. 2. General Ledger Detail / Sales Subledger Detail: Input data sheets containing data from source data files provided by the client. Please use the following sheet for reference: 3. Fields vs Visuals: This sheet shows specific data fields being used for each tab within the Power BI file. Please do not alter the following sheets, they are formula driven: 4. Input Parameters: Contains project detail information from the ‘Request’ sheet. 5. Mapping Parameters: Contains field mapping information from the ‘Request’ sheet. 6. Materiality: Contains materiality and clearly trivial threshold information from the ‘Request’ sheet. 7. Fiscal Calendar: Contains fiscal calendar mapping information from the ‘Request’ sheet.

Power BI

1.

This dashboard was created using June 2020 Power BI software. Use June 2020 or a newer version in order to open this Power BI file. To check your version, select File > About. Reference screenshot to the right.

2.

Some dashboards may contain multiple sections, requiring the user to scroll. Check to see if the dashboard requires scrolling to view additional visualizations before moving on to the next dashboard.

Power BI 2. Data Preparation (Request) This dashboard is designed to have two periods of data.

A Open PowerBI – Revenue - Industry Agnostic Revenue – Analysis – Data Request Form.

Go to ‘Request’ sheet. Fill in only the green cells with the required information.

B The tables to the right shows optional and required fields for this analytic. Please use only the ‘Document Type’ values noted in the field description. The Power BI visuals will break or remain empty if other values are used within these fields.

Please see the following pages for instructions on how to fill out the ‘General Ledger Detail’ and ‘Sales Subledger Detail’ sheets in PowerBI – Revenue - Industry Agnostic Revenue- Analysis – Data Request Form .

i

Incomplete or inaccurate information in green cells of this sheet may cause broken or inaccurate visualizations in the Power BI dashboard.

Power BI 2. Data Preparation (Continued) - (General Ledger Detail) This dashboard is designed to have two periods of data.

C

Go to the “General Ledger Detail” sheet. Below are instructions to bring the entity’s data into this sheet using Excel. For larger data sets, consider using ACL to prepare data using similar logic.

Required Fields for General Ledger Detail

Field Type

Entity

Character

Effective Date

Date

GL Account Number

Character

GL Account Name

Character

Amount

Numeric

The tables to the right show the fields that are required for this analytic. Check the format for each column in the source data files. Ensure they match the formats shown in the table to the right.

1. Open source data files received from Client. 2. Copy data from relevant fields from ‘Required Fields for Analysis’ columns above (Excluding row 1 column headers) from Current Period source data file to the “General Ledger Detail” sheet in the PowerBI – Revenue - Industry Agnostic Revenue – Data Request Form under Columns A – E. 3. Repeat previous step for Prior Period data, adding the data directly below Current Period data. 4. Save PowerBI – Revenue - Industry Agnostic Revenue – Data Request Form. Please refer to image on the next page showing an example of summarized data in the ‘General Ledger Detail’ sheet that will be loaded into Power BI.

Power BI 2. Data Preparation (Continued) - (General Ledger Detail) This dashboard is designed to have two periods of data.

Confirm the columns are populated from source data files with the correct values under the specified column names. If missing optional fields, do not delete the columns, leave them blank. If missing required fields, refer to ‘Fields vs Visuals’ sheet in PowerBI – Revenue - Industry Agnostic Revenue - Analysis– Data Request Form to understand specific data fields being used for each visual.

Do NOT add columns, delete columns or change column headings in any sheet – this may result in errors.

Power BI 2. Data Preparation (Continued) - (Sales Subledger Detail) This dashboard is designed to have two periods of data.

D

Go to the “Sales Subledger Detail” sheet. Below are instructions to bring the entity’s data into this sheet using Excel. For larger data sets, consider using ACL to prepare data using similar logic.

The tables to the right show the fields that are required and optional for this analytic.

Check the format for each column in the source data files. Ensure they match the formats shown in the table to the right.

Required Fields for Analysis Sales Subledger Detail

Field Type

Entity

Character

Effective Date

Date

Customer

Character

Document Number

Character

Document Type

Character

Product or Service

Character

Sale Amount

Numeric

Optional Fields for Analysis - Sales Subledger Detail

Field Type

Product or Service Type

Character

Quantity

Numeric

1. Open source data files received from Client. 2. Copy data from relevant fields from ‘Required Fields for Analysis’ columns above (Excluding row 1 column headers) and ‘Optional Fields for Analysis’, if applicable, from Current Period source data file to the “Sales Subledger Detail” sheet in the PowerBI – Revenue - Industry Agnostic Revenue – Data Request Form under Columns A – I. 3. Repeat previous step for Prior Period data, adding the data directly below Current Period data. 4. Save PowerBI – Revenue - Industry Agnostic Revenue – Data Request Form. Please refer to image on the next page showing an example of summarized data in the ‘Sales Subledger Detail’ sheet that will be loaded into Power BI.

Power BI 2. Data Preparation (Continued) - (Sales Subledger Detail) This dashboard is designed to have two periods of data.

Confirm the columns are populated from source data files with the correct values under the specified column names. If missing optional fields, do not delete the columns, leave them blank. If missing required fields, refer to ‘Fields vs Visuals’ sheet in PowerBI – Revenue - Industry Agnostic Revenue - Analysis– Data Request Form to understand specific data fields being used for each visual.

Do NOT add columns, delete columns or change column headings in any sheet – this may result in errors.

Power BI 3. Import Data into Power BI

A Open PowerBI – Revenue - Industry Agnostic Revenue – Analysis file.

B Click on the bottom part of the “Transform data” icon in the Queries section of the ribbon and select “Data source settings”.

C Click on “Change Source”.

Power BI 3. Import Data into Power BI (Continued)

D Click “Browse” and find PowerBI – Revenue Industry Agnostic Revenue – Data Request Form from the saved location.

If not already selected, select “Excel Workbook” under ‘Open file as’. Click “OK” then select “Close”.

E Click “Refresh”.

i

Upon successful import, there will not be any error messages and the tie-out in the Background tab can be used to ensure the totals in the analytic tie out to the source data.

Power BI 3. Import Data into Power BI (Continued) – Potential Errors

i The error message to the right may be received if a required column was changed or does not exist. Please ensure ALL original column names in the ‘General Ledger Detail’ and ‘Sales Subledger Detail’ exist unmodified.

i The error message to the right may be received if a sheet does not exist. Please reference the data request form during import and retain ‘General Ledger Detail’ and ‘Sales Subledger Detail’ sheet name.

i The error message to the right may be received if one of the quantitative fields contains a character instead of a number. Please check the data and replace it with either a number or a blank cell. Click on “View Errors” which opens Query Editor to learn more about which row number and column contains the error(s). 

Power BI 4. Reconcile Imported Data to Source Data

A Click on the Data Icon and view the first few records to ensure the data was imported. To navigate to the main data, on the right hand side of this view under ‘FIELDS’ select ‘General Ledger Detail’ and ‘Sales Subledger Detail’.

i If the data doesn’t look like the data received by the entity, please go back to Step 3 to try to import the data again.

B Click on the Report Icon to view the visualizations.

i

If source data totals do not match imported totals in the Background tab, it is possible that data is missing from the analytic or being double counted. Please check to make sure mapping and data preparation was done correctly in Step 2 and reimport the data using the instructions found in Step 3.

Power BI 4. Reconcile Imported Data to Source Data (Continued)

C

Navigate to Background tab on the dashboard.

Do not view any visualizations until it is confirmed that all of the source data was imported. Compare totals imported in Background dashboard to the source data received from the client.

i

If source data totals do not match imported totals in the Background tab, it is possible that data is missing from the analytic or being double counted. Please check to make sure mapping and data preparation was done correctly in Step 2 and reimport the data using the instructions found in Step 3.

Power BI 5. Tableau vs Power BI Differences

A

Additional information about each dashboard can be found differently in Power BI versus Tableau. Key information found through hovering over special icons includes the Risk Factors and GRA Question(s) relevant for a dashboard, the audit purpose of the dashboard, helpful information for the dashboard and special filter / parameter information as applicable. In Power BI, all of this information can be found from hovering over the Risk Factor found on the top left-hand side of the dashboard. In Tableau, this information is found within separate icons. See comparison below.

Power BI

Tableau 



Risk factor

Audit Purpose

Helpful Tips

Special Information

B

In Power BI, the tabs after the “End of Analytic” tab contain the tooltip text visible when hovering over the Risk Factor on each dashboard.

Power BI 5. Tableau vs Power BI Differences (Continued)

Dashboard – Tab 5 

C

In Tableau (left) this dashboard is set up in 2 sections using a ‘View By’ parameter to change views, while it’s displayed as 3 separate sections in Power BI (right). In Tableau (left) Sales Amount & Transaction Volume are displayed in separate sections, while in Power BI (right), the user has the ability to toggle between Sales Amount, Transaction Volume and/or Number of Customers/ Number of Product or Service in the respective section. Materiality and CTT is displayed as a constant line within the charts in Tableau (left) while it is displayed next to the visual in Power BI (right). In Power BI (right) instead of a scatter plot, a bar chart and detail table must be used to represent aggregation of calendar day, Customer, and Product or Service. The bars are color coded based on Materiality and Clearly Trivial Threshold.

Tableau 

Power BI 

Power BI 5. Tableau vs Power BI Differences (Continued)

Dashboard – Tab 7 

D

In Power BI (top) the bar chart is laid out differently from Tableau (bottom). Instead of having two sections of bars with prior period bars in one area and current period bars in another, Power BI has current period and prior period bars side by side. Power BI (top) cannot allow you to see both amount and percentage at the same time, so the user will need to toggle between the two views using the ‘View’ parameter. In Power BI (top) there is no expand collapse functionality.

Power BI 

Tableau ...