Share via

Benford's Law Function in PowerPivot

  • Article

Welcome to My Wiki Articles on PowerPivot Section.

I would want to explain the Data  Analysis and Fraud Investigations functions in my Series of Wiki Articles on PowerPivot So dont forget to check my other Articles on Wiki to check all the Data Analysis and Fraud Analysis Functions.

This is the Second Article of such Series.

How to Perform Benford Analysis in PowerPivot.

Here I would like to explain How Benford's First Digit Analysis is performed in PowerPivot

Step 1: Import the Source Data into PowerPivot. In this Example I have created a Linked Table from excel called "Sales" Table. and I want to perform the Analysis for Sales Field in this Sales Table.

Step 2: Create a BenfordFirstDigit Table Manually in excel with two Fields FirstDigit and BenfordPercentage.

Step 3: Add a Calculated Column FD, in PowerPivot Sales Sheet. This Column should Extract the First Digit from the Sales Column.

The Formula for that calculated column is 

=INT(mid(Sales[Sales],1,1))

Step 4: Now Create a RelationShip between the FirstDigit Column in BenfordFirstDigit Table and FD Column in Sales Table.

Step 5: Now Add a Pivot Table.

With FirstDigit Field from BenfordFirstDigit Table into Row data, BenfordPercentage into Values(Sum), FD from Sales Table into values (Count).
We Will Get a Pivot Table as shown in the Figure Now from this data in Excel Create a Benford results Table.

Step 6:

Where

FirstDigit = "Row Labels" from above Table.
BenfordPercentage = "Sum Of BenfordPercentage" from above table.
Actual Occurances = "Count of FD" from above table.
Benford Occurances = BenfordPercentage * 6664/100 (Where 6664 are the total number of records in the source Data).
Occurrence Difference= Benford Occurrence - Actual Occurrence
Actual Percentage=(Actual Occurrence/6664)*100
Percentage Difference= Benford Percentage - Actual Percentage

Similarly even the Variance of Percenatage can be calculated by =(Percentage Difference/Benford Percentage) *100

Hope This Helps. Will write a separate wiki Article for the Analysis of Second Digit Analysis, Third Digit Analysis, Fourth Digit Analysis, First two Digit Analysis, First Three Digit Analysis.

If some one need it right away please let me know. WIll write that on high priority basis.

Hope it helps.

Cheers.

ManjunathRV