We'll show you how to get the kpis values here is the data set let's begin by creating the pivot tables go to insert menu and create pivot.
Table add feedback ID to the values field and change the field calculation to count instead of sum we will use this data in the dashboard hence let's input total cases in above cell and format it.
Is shown in the tutorial now give reference of pivot table count for total cases value also we will use if error in case of error repeat the steps for other values let's.
Add this data to the dashboard insert a text box from the insert menu give the reference of value from pivot table in Formula bar the last update date and comments.
Pending cases within SLA insert a pivot table and add the full case status to the rows field and the values field use the vlookup formula to get these two specific cases.
Counted then sum them to get all pending cases within SLA finally calculate the ratio between complaints and non-c complaints next the current cases.
Status we will initially create a donut chart for status of current cases let's insert stage to rows and values field we will insert the status to row field again for inserting proportion.
Percentage of cas's status by by changing the calculation to a percentage of column total now we will insert the donut chart using this.
Table then move the chart to the dashboard next the resolved cases create a pivot table by inserting month in the row field and feedback ID in the values field now change the.
- Create Best CV in MS Word Best CV Template 2022 DOWNLOAD FREE
- Drive efficiencies in invoice processing for finance departments with Microsoft Power Automate
- Dinamic & Interactive Dashboard no Excel com tabelas pivot e gráficos de pivô Sem macros.
Feedack back ID calculation to - ID Card Make
Count we will now create a table for dashboard reference let's input month in the First Column then input the value using vlookup function from the pivot.Table now we want to highlight the month with maximum and minimum value hence we will use if in Max formula as shown repeat the steps for displaying minimum.
Value by replacing Max formula with Min formula let's insert a clustered column chart for monthly resolved cases it's time to interestingly highlight the chart with maximum and minimum.
Values select the minimum Point column in the chart and change the series overlap to 100% and change the color to Green repeat the steps for maximum Point column in the chart and change the color.
To Red remove all the chart labels and legends repeat the steps for minimum value by replacing red gradient to Green.
Gradient we will also calculate the average monthly cases and total cases per quarter using average function for all cases and some function for every 3.
Months for each quarter next banking with part we will initially create a pie chart for banking with status insert banking with.
Status to rows field and values field we will insert the banking with status to row field again for inserting proportion percentage of status by changing the calculation to percentage of column.
Total now we will insert the pie chart using both this table and format at High liting main branches in the first chart and online banking in the second chart let's move both these charts to.
The
Dashboard next the customer type part insert customer to row field and the values field let's insert the table again as done before for reference of.Values in the dashboard it's time to input the customer values using vlookup function from the pivot table now insert the icons for customer types from the insert menu by searching.
For the relevant icons next the analysis per region insert departments to row field and SLA and csat to the values field change the calculation of SLA by area as.
A percentage instead of direct values now we will create a summary with same format as used before for dashboard reference let's input the field by using vlookup formula we will input all the.
Values of region names y TDS laa percentage and CES on the dashboard for creating a regional view we will insert an online map image from insert menu and searching for online.
Pictures next the feedback part let's create a pivot table by inserting feedback type status to Rose Field and values field let's insert the feedback to Rose Field again for.
Calculating proportion percent of feedback type we will change the feedback value field to count we need to insert additional pivot tables for monthly values of each.
Feedback type let's insert the feedback type field to values and filters field and month to Row's field start with creating table for complaint values in the dashboard and.
Hence filter the pivot with complaint feedback initially we will give reference for total feedback back count then we will find feedback count of selected feedback type from previous.
Pivot table using vlookup function now we will repeat the steps to display feedback percentage that's all for today's video hope you learned something useful for.
You join our Channel membership and enjoy the full video tutorial on the other hand you have an easy way to directly purchase this.
