Tag Archives: Excel

Quick way to enable PowerPivot tab

Sometimes PowerPivot/Excel crashes or hangs, and you need to kill the Excel process. When you open Excel again, you notice that the PowerPivot tab doesn’t show up.

Here’s a quick way to re-enable the PowerPivot tab.

1. First, we add the COM Add-Ins button to Quick Access Toolbar. This is done only once. Customize Quick Access Toolbar to add COM Add-Ins…

Picture1

2. From Developer Tab, choose COM Add-Ins… Add, OK.

Picture2

 

3. If the PowerPivot tab, for any reason, becomes unavailable; click on the COM Add-Ins button.

Picture3

4. Check the PowerPivot checkbox, click OK.

Picture4

 

PowerPivot KPI for Different Category Range

Suppose we have two simple related tables, ‘Table’ and ‘Service Code’. Main table contains transactions with accounts/dates/consumption amount by Service Code. The Service Code table contains the Min and Max ranges for service codes that will be used to analyze the average consumption. The Min/Max values differ for every service code and we want to create a KPI that will indicate if the average consumption falls within the range specified for the service code in question.

1. Import your data into the PowerPivot Model.

clip_image002

clip_image003

2. Create the relationships between the tables.

clip_image004

3. Create a calculated field for Average Consumption

clip_image005

4. Add two related columns for Min and Max values into the transaction table by using the PowerPivot RELATED() function. Note: this function will only work if you created the appropriate relationship in step 2. A good alternative for the RELATED() function is PowerPivot LOOKUPVALUE() if your tables are not related.

clip_image007

5. Add two calculated fields to find the minimum MIN value and maximum MAX value across the board using the MIN() and MAX() function on the respective columns.

clip_image009

6. Now for the fun part, create a calculated field that will provide us with a ‘Boolean’ value indicating if the Average Consumption (calculated field from step 3) falls between the Min and Max calculated fields from the previous step.

Consumption in range:=if([Average Consumption]=0,BLANK(),IF(AND([Average Consumption]>=[Service Code Min],[Average Consumption]<=[Service Code Max]),1,0))

clip_image011

7. Create KPI on the new calculated field.

clip_image013

8. Set the absolute value to 1 and move both min and max targets to 1 (that is 0.999 for the min value)

clip_image015

9. Cleanup the model by hiding the unnecessary columns/fields/table from client tools.

clip_image017

clip_image018

clip_image019

10. Create PivotTable.

clip_image020

11. The KPI Status column will provide the indicators showing if the average consumption falls in the ranges specified in the Service Code table for every service code.

clip_image022