Power BI

Fraude signaleren met Benford’s Law in Power BI

Benford’s Law is het verschijnsel dat in een dataverzameling van getallen de eerste cijfers van deze getallen op een speciale wijze verdeeld blijken te zijn. Je zou wellicht verwachten dat elk eerste cijfer van 1 tot en met 9 een even grote kans heeft om voor te komen, maar dat blijkt niet het geval te zijn.

In werkelijkheid komt het cijfer 1 vaker voor en het cijfer 9 het minst vaak.

Dit kan gebruikt wordt om onregelmatigheden, vervorming van data of frauduleuze handelingen te ontdekken. Door de verdeling van de eerste cijfers van een dataset te vergelijken met de verdeling volgens Benford’s Law kunnen onregelmatigheden ontdekt worden, die verder onderzocht moeten worden.

De ISACA (Information Systems Audit and Control Association) heeft een uitstekend artikel geschreven met een uitleg over het gebruik van Benford’s Law te vinden via de volgende link: https://www.isaca.org/Journal/archives/2011/Volume-3/Pages/Understanding-and-Applying-Benfords-Law.aspx

In deze blog laat ik zien hoe je Benford’s Law toepast in een Power BI dashboard.

Stap 1 Toevoegen Benford’s distributie tabel

Open in Power BI de Query Editor. Maak via New Source een ‘Blank Query’ aan.

Hernoem de query naar Benford’s law/

00 rename query

Start de Advanced Editor.

01 adv editor

Voer de volgende code in:

let
Stap1 = Table.RenameColumns(Table.FromList(List.Numbers(1,9), Splitter.SplitByNothing()),{{“Column1”, “Cijfer”}}),
Stap2 = Table.AddColumn(Stap1, “Log10″, each Number.Log10(1+1/[Cijfer]), type number),
#”Renamed Columns” = Table.RenameColumns(Stap2,{{“Log10”, “Verwacht percentage”}})
in
#”Renamed Columns”

Het resultaat is een tabel met Benford’s distributie:

02 benfrod distrubiute

Eerste cijfers bronbestand

Als bron bestand heb ik een auditfile vanuit een financieel administratie pakket gebruikt.

Het bronbestand heet in dit voorbeeld trLine2016:

Middels de optie Add column – > extract -> First character heb ik van elk getal het eerste cijfer in een aparte kolom genaamd ‘Digit’ geplaatst.

04 extact

05 first chartcter

Groeperen en tellen van werkelijke aantallen

Met de volgende code groeperen we de getallen in de kolom Digit en berekenen we het aantal keer dat het getal voorkomt in de kolom Actual:

Stap4 = Table.Group(Stap3, {“Digit”}, {{“Actual”, each Table.RowCount(_), type number}}),

07 stap 4

Berekenen totaal 

In de volgende stap rekenen we het totaal aantal getallen uit, zodat we de verdeling per cijfer kunnen uitrekenen.
Hiervoor gebruiken we de volgende code:

Stap5 = Table.Group(Stap4, {}, {{“AllOccurences”, each List.Sum([Actual]), type number}}),
AllOccurences = Stap5{0}[AllOccurences],

 

08 all occur

Samenvoegen met Benford’s distributie

In stap 6 voegen we per getal de verwachte waarde volgende Benford’s distributie samen met de werkelijke aantallen per cijfer:

Stap6 = Table.NestedJoin(Stap4,{“Digit”},#”Benford’s law”,{“Cijfer”}, “NewColumn”,JoinKind.RightOuter),
#”Expanded NewColumn” = Table.ExpandTableColumn(Stap6, “NewColumn”, {“Cijfer”, “Verwacht percentage”},
{“NewColumn.Cijfer”, “NewColumn.Verwacht percentage”}),
#”Renamed Columns” = Table.RenameColumns(#”Expanded NewColumn”,{{“NewColumn.Verwacht percentage”, “Verwacht percentage”}}),

09 samengevoegd

Verwachte aantallen

In volgende stap rekenen we de verwachte aantallen per cijfer uit door het totaal aantal getallen te vermenigvuldigen met Benford’s distributie. Onderweg verwijderen we ook twee kolommen die we niet meer nodig hebben.

Stap7 = Table.RemoveColumns(#”Renamed Columns”,{“NewColumn.Cijfer”}),
Stap8 = Table.AddColumn(Stap7, “Verwacht”, each [Verwacht percentage]*AllOccurences, type number),
Stap9 = Table.RemoveColumns(Stap8,{“Verwacht percentage”})

10 expected

Visualiseren in Power BI

We hebben nu genoeg data om Benford’s Law te visualiseren in Power BI.

Voeg een Bar Line and column stacked visual toe aan het rapport.

11 visual

Voeg de data velden toe aan de vsualisatie:

  • Digit onder Shared Axis
  • Actual onder Column values
  • Verwacht onder Line Values

12 data velden

 

12 grand total

Power BI laat nu een mooie grafiek zien van de werkelijke en verwachte verdeling van de eerste cijfers volgens Benford’s Law.

14 gchart

Meer weten over onze oplossingen?

Onze consultants hebben veel ervaring binnen een grote verscheidenheid aan branches.
Eens verder brainstormen over de mogelijkheden voor jouw organisatie?

Maak kennis met onze specialist Arnoud van der Heiden.

Wellicht herkent u een van deze vraagstukken.

Inmiddels hebben wij ruime ervaring opgedaan met Business Intelligence. Wij staan ook u graag te woord bij vragen of opmerkingen.

3 reacties. Leave new

Geef een reactie

Uw e-mailadres wordt niet gepubliceerd. Vereiste velden zijn gemarkeerd met *

Fill out this field
Fill out this field
Geef alstublieft een geldig e-mailadres op.

NEWSFLASH

Maandelijks op de hoogte blijven van het belangrijkste IT-nieuws voor het MKB?