Three ways to check your Power Pivot results.

Power Pivot is a good personal Business Intelligence tool. It should help you to give you better insight in what is happening in your company. The goal is to give insight, so you can take action to do it better in the future. Analyzing the data can results in 3 situations

1. Results you expect.
2. Positive results you do not expect
3. Negative results you do not expect.

In case of situation 3: ‘Negative results you do not expect.’ people will doubt on the correctness of the report. This blog post will help you to analyze the correctness of the data in more detail. In my previous blog I explained the basics of Power Pivot. Pleas read this document before using this blogpost.

1) Add slicers to your report to get a dataset you can understand.

PivotTables and PivotCharts will show consolidated data. This can be a total of thousand or more records. Try to lower the number of records so you can understand the end result in the PivotTable or PivotChart. This can be done by defining some additional slicers which you can use to filter the data. For instance in a revenue report add the country, state and representative to the PivotTable. Now you can look to the revenue report for only one employee, in one country and one state.

How to add slicer to the PivotChart?

Select the Pivot Chart, Open tab PivotChart Tools Analyze.
Press the Insert Slicer button in the ribbon.
Select the country field from the debtor dataset.

Click to enlarge

Repeat these steps for other slicers like statecode or representative.

2) Add filters in Power Pivot window

The Power Pivot Window will show the import dataset records. In the Power Pivot you can filer the dataset to analyze a smaller set of data. For instance for year 2012 and period 6

Open the Power Pivot window. Open PowerPivot tab and select PowerPivot Windows button in the ribbon.

Click to enlarge

Select the dataset you want to filter. For instance InvoiceHistory. Select the drop down button in the column Invoice Year. Select only 2012. Press OK.

Click to enlarge

 Repeat these steps for other columns in the dataset. Look if you got the records you expect.

3) Use query analyzer to add filters to the view.The last step you can do is to analyze the dataset itself in the SQL Server Management Studio. (SSMS) .  For this you need to have knowledge of building TSQL queries.
First of all we need to know the query of the dataset in PowerPivot to import the data.
Select the dataset tab. Select Design tab, Press Table Properties button. In the Edit table properties window switch to: Query editor.

Click to enlarge

Select the query and copy it to your clipboard. (CTRL C)
Open SSMS, Open new query windows and connect to the database. Paste the query from your clipboard to the query window.
Now you can add WHERE clauses, JOIN’S with other tables etc  to the query.