diff options
Diffstat (limited to 'content/blog/2024-01-26-audit-dashboard.md')
| -rw-r--r-- | content/blog/2024-01-26-audit-dashboard.md | 150 |
1 files changed, 0 insertions, 150 deletions
diff --git a/content/blog/2024-01-26-audit-dashboard.md b/content/blog/2024-01-26-audit-dashboard.md deleted file mode 100644 index fabb0e3..0000000 --- a/content/blog/2024-01-26-audit-dashboard.md +++ /dev/null @@ -1,150 +0,0 @@ -+++ -date = 2024-01-26 -title = "Building an Audit Status Dashboard" -description = "" -draft = false -+++ - -Alteryx and Power BI are powerful tools that can help turn your old-school audit -trackers into interactive tools that provide useful insights and potential -action plans. - -With these tools, we are going to build a dashboard that can effectively -communicate project status. - -# Requirements - -This project assumes the following: - -- You have access to Alteryx Designer and Power BI Desktop. - - If you only have Power BI Desktop, you may need to perform some analysis - in Power BI instead of Alteryx. -- Your data is in a format that can be imported into Alteryx and/or Power BI. -- You have a basic understanding of data types and visualization. - -# Alteryx: Data Preparation & Analysis - -## Import Data - -With Alteryx, importing data is easy with the use of the `Input Data` tool. -Simply drag this tool onto the canvas from the `In/Out` tab in the Ribbon to -create it as a node. - -You can choose the File Format manually or simply connect to your file/database -and let Alteryx determine the format for you. For this example, we will be -importing an Excel file and changing the `Start Data Import on Line` variable to -`2`. - -## Transform Data - -Next, let's replace null data and remove whitespace to clean up our data. We can -do this with the `Data Cleansing` tool in the `Preparation` tab in the Ribbon. - -Ensure that the following options are enabled: - -- Replace Nulls - - Replace with Blanks (String Fields) - - Replace with 0 (Numeric Fields) -- Remove Unwanted Characters - - Leading and Trailing Whitespace - -For our next step, we will transform the date fields from strings to datetime -format. Add a `Datetime` tool for each field you want to transform - in the -example below, I am using the tool twice for the "Started On" and "Submitted On" -fields. - -Now that the dates are in the correct format, let's perform a calculation based -on those fields. Start by adding a `Filter` tool, naming a new Output Column, -and pasting the formula below into it (the two fields used in this formula must -match the output of the `Datetime` tools above): - -```txt -DateTimeDiff([SubmittedOn_Out],[StartedOn_Out], "days") -` - -## Export Data - -Finalize the process by exporting the transformed data set to a new file, for -use in the following visualization step. - -# Power BI: Data Visualization - -## Import Data - -To start, open the Power BI Desktop application. Upon first use, Power BI will -ask if you want to open an existing dashboard or import new data. - -As we are creating our first dashboard, let's import our data. In my example -below, I'm importing data from the "Tracker" sheet of the Excel file I'm using -for this project. - -During this process, I also imported the export from the Alteryx workflow above. -Therefore, we have two different files available for use in our dashboard. - -## Add Visuals - -To create the dashboard, you will need to follow the list instructions -below and format as needed. - -Instructions to create the visuals above: - -- `Text Box`: Explain the name and purpose of the dashboard. You can also add - images and logos at the top of the dashboard. -- `Donut Chart`: Overall status of the project. - - `Legend`: Status - - `Values`: Count of Status -- `Stacked Column Chart`: Task count by assignee. - - `X-axis`: Preparer - - `Y-axis`: Count of Control ID - - `Legend`: Status -- `Treemap`: Top N client submitters by average days to submit. - - `Details`: Preparer - - `Values`: Sum of Avg~DaysToSubmit~ -- `Line Chart`: Projected vs. actual hours over time. -- `Clustered Bar Chart`: Projected vs. actual hours per person. -- `Slicer & Table` - Upcoming due dates. - - `Slicer`: - - `Values`: Date Due - - `Table`: - - `Columns`: Count of Control ID, Date Due, Preparer, Status - -## Format the Dashboard - -You can choose a theme in the View tab of the Ribbon. You can even browse for -custom JSON files that define themes, such as ones found online or custom ones -created by your organization. - -For each visual, you can click the `Format` button in the `Visualizations` side -pane and explore the options. You can custom options such as: - -- Visual - - Legend - - Colors - - Data labels - - Category labels -- General - - Properties - - Title - - Effects - - Header icons - - Tooltips - - Alt text - -You can always look online for inspiration when trying to decide how best to -organize and style your dashboard. - -# Sharing the Results - -Generally, you have a few different options for sharing your dashboards with -others: - -1. Export the dashboard as a PDF in the file menu of Power BI. This will export - all tabs and visuals as they are set when the export button is pressed. You - will lose all interactivity with this option. -2. Send the full Power BI file to those you wish to share the dashboard. This - will retain all settings and interactivity. However, you will also need to - send the source files if they need to refresh the dashboard and you will need - to re-send the files if you make updates. -3. Store the dashboard in a synced location, such as a shared drive or Microsoft - Teams. Depending on how a user configures their local Windows paths, the data - source paths may not be compatible for all users with such a setup. |