Frequency and normal distribution calculation

[Nazram]

First of all thank you all for your time.

I am sure that this question is pretty basic and it have been answered before but I couldn't find it at the forums. So sorry for repeating myself if that it is the case.

I have this array of data (as an example):

 

I want, directly in PowerBI, to calculate the frequency of the number of "Underlings" according to a bin that I specify.

Additionally, I will also want to calculate the normal distribution of this "Underlings" data (maybe this example does not follow the distribution but the real data does). At this moment I can do this easy at excel and then import another table. The final result calculated in excel look like these:

 

In case anyone wonder I want to these directly in PowerBI in order to be able to navigate throw the different filters and "families" associated to the "Overlords" (not included at this example). So the frequency also filters when I change "families" and are not a static image of the whole data.

[v-danhe-msft · Accepted solution]

Hi @Nazram,

Based on my test, you can refer to below steps:

1.I have entered some sample data to test for your problems. Table1 is the data I entered below in the picture. Table2 covered the data of [bin].

             

2.Create a calculated column to calculate your frequency in 'Table2'. Create a table visual to show your [bin] and [Frequency].

Frequency = IF(ISBLANK(COUNTROWS(FILTER(Table1,VALUE(Table1[Underlings])=VALUE(Table2[Bin])))),0,COUNTROWS(FILTER(Table1,VALUE(Table1[Underlings])=VALUE(Table2[Bin]))))

3.Create four new measures to calculate the average, the Variance and the Variance range.

Mean = AVERAGE(Table1[Underlings])

Standard DEV = STDEV.S(Table1[Underlings])

X-3a = [Mean]-3*[Standard DEV]

X+3a = [Mean]+3*[Standard DEV]

4.Create a new table to calculate the normal distribution.

‘Normal distribution’ =

var minvalue=FLOOR([X-3a],1)

var maxvalue=CEILING([X+3a],1)

return SELECTCOLUMNS(CALENDAR(minvalue,maxvalue),"x",INT([Date]))

5.Create a calculated column to calculate the normal distribution value, and a new measure to calculate the ration.

z = ('Normal distribution'[x]-[Mean]/[Standard DEV])

f(x) =  EXP((VALUES('Normal distribution'[x])-[Mean])^2/(2*[Standard DEV]^2)*-1)/(SQRT(2*PI())*[Standard DEV])

6.Create a Table visual to add the [x] and the [z] fields. And modify the "Total" off. Then add the [f(x)] field.

Now you can get your "Frequency" and "Nominal distribution".

You can also download the PBIX file to have a view.

https://www.dropbox.com/s/vs2uu8grdb038r6/Frequency%20and%20normal%20distribution%20calculation.pbix...

Regards,

Daniel He

[Nazram]

Thanks @v-danhe-msft,

This exactly what is was looking for. And at the same time you teach me a lot of things of PowerBI. I am relatively new and programming was never my strong point. Thanks for your time and effort.