Things to Remember About KPI Dashboard in Excel Tada, your KPI Dashboard is ready and will be looking as shown in the screenshot below. Step 20: Click on the OK button once you are done with setting the KPI parameters and customization. Step 19: Now, we can select icon style as well (These are the KPI Icons). Change the Status Threshold values as shown in the screenshot below. Step 18: In this step, define the Status Threshold value. We are doing this because we don’t have any other target values/measures defined as separately to have a comparison with actual values of Margin %. Step 17: In this window, define an Absolute Value for the target as 1. It has some by default values, which we are going to change. Select the New KPI option.Ī KPI window will pop up, as shown below. Click on the KPIs tab as shown in the figure. Step 16: Now our data is ready for KPI’s to be added. Step 15: Select the entire column, Margin %, and convert it to % using number formatting. Once you are done with this step, click on the OK button, and you will be able to see a new calculated column named Margin% under the pivot table. Here, we have created a new calculated column named Margin%, which can be formulated as the ratio of two columns, Sum of Margin and Sum of Sales. Under the Formula section, add the formula =/ as shown below. If you want to add a description for the same, you can add it there as well under the Description section. Step 14: Under ” Measure name:”, add a name for your measure (For, e.g. It will allow you to formulate a measure and add it under your pivot table created. Click on New Measures.Ī new window called Measure will pop up. Step 13: Now, we are going to add a calculated column in the pivot. Here we have selected data in the following order: Step 12: Select the data under rows, columns, and values, as shown below. I will prefer to go with the same sheet and then will provide the range on which the pivot table should be added. Once you click it, a new window named Create PivotTable will appear in which you have to select the data from PowerPivot, and it asks you whether you want a pivot table under a new sheet or on the same sheet. Click on the Pivot Table tab under PowerPivot. Step 11: Now, in order to create a KPI report, we need to slice and dice our data under the pivot table. Step 10: After a successful import, you will be able to see the data as below. Step 9: Click on the Finish button once the data table is loaded into PowerPivot. Step 8: Browse the path on where your excel data file is stored and click the Next button to finish the Import. Select the Excel file, then click on the Next button to proceed further. There you can see a lot of options from where you can import the data to PowerPivot. Step 7: Once you click on the “ From Other Sources” tab, a new window named Table Import Wizard will pop up. However, if you have an excel file or text file or some data files other than SQL and Access, please click on From Other Sources tab. You can click the Database tab if you want to add data from SQL Server, Access. There are different ways to import data under PowerPivot. Step 6: It should open a new tab named Power Pivot for Excel. Under the PowerPivot tab in your excel file, click on Manage. Select Microsoft Power Pivot for Excel from the list of add-ins and click OK.Īfter enabling this PowerPivot add-in, you should be able to see the PowerPivot option tab on the topmost panel ribbon in your excel. Step 4: As soon as you hit the Go button, the COM Add-ins dialog box will pop up. Step 3: Select COM Add-ins under the Manage section dropdown and click on the Go button.
0 kommentar(er)
