Dax measure for Benford Laws Analysis

[PhilippeMuniesa]

Hello,

I'm new to PowerPivot and DAX, and I'm carting a solution to analyze a value table (accounting writings) and compare the distribution of the first digit of this table with the Benford distribution.

I also want to do this analysis from several axes (period, audit cycle, account number etc) via slicers.

I started, but I aim at creating several measures:

1 - The total of distribution according to the line context,

2 - and secondly, the calculation of the theoretical distribution benford, the previous measure multiplied by the Distribution Benford by digit.

I built a small excel table with the 3 base used, the relationships made and a sheet to understand what I want to get.

https://www.dropbox.com/s/ayolw2oam3ff1kh/Benford.xlsx?dl=0 

If I can get help with this project, thank you in advance.

PS excuse my English (I'm French)

Philippe Muniesa

[amitchandak]

@PhilippeMuniesa , I checked the excel. Can you let me know the specific challenges you are facing?

[PhilippeMuniesa]

Hello,

thank you for helping a French beginner alone ....

If you look at the tab "TCD_result"  you will see 2 columns outside the TCD, that I can't get the formula with DAX.

The first is the measurement that allows me to get the total of the FirsDigit$column, depending on the context of lines, this measure I lack to make the second measure which consists of multiplying the line context of the Distribution column by the measure mentioned above (see the Column D column).

I hope I've been clear, but I think it will be clearer if you download the excel file and if you look into powerpivot and the TCD sheet that exploits the data loaded into powerpivot.

thank you in advance for your help