Calculated Column (Probability of Exceedance) Using Earlier Values

[M4dsteve]

Hi all,

I am having some difficulty with a calculation that is used to calulate a probability of exceedance in a given data set.

Long story short this is for the Industrial Hygiene industry and we are using a Sharepoint List to collect information that is then transformed and presented in Power BI.

Currently Excel is used to calculate this value which is easy mode since you can just reference a cell beneath but my brain is struggling to find a way to do this in PBI. 

Sample table from Excel With PDEP being the column I am trying to calculate.

"Formula from cell I4" =i5+(1-i5)*@Na/(@Na+@Nbj)

NA is the range of column 'c' values 

Nbj is the range of column 'h' values

The formula is continued down the column with the 'i' values being the cell below.

I have created the table to an extent but in creating the calculated column I (of course) cannot reference itself. 

PDEP = 
    VAR Previous = if('ND Summary'[Pack ID]=MAX('ND - Upside Down'[Pack ID]),0,EARLIER([PDEP],1))
    return Previous+(1-Previous)*'ND Summary'[Values]/('ND Summary'[Values]+'ND Summary'[N bj id])

There must be a smart way of doing this out there so I appreciate all of your help.

[v-janeyg-msft]

Hi, @M4dsteve 

I don’t understand your logic very well, but this seems to be a recursive problem. If it involves recursive calculations, Dax function is difficult to do due to the limitations of the dax language, but excel is simple. You can calculate it in excel first, and then bring data into powerbi, it may be the easiest way.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[M4dsteve]

Thanks @v-janeyg-msft,

Yes you are correct in that the 'i' reference is the problem here as it is calculating up the spreadsheet with the values in the row below.

I began with this table turned upside-down in Power BI and had an IF statement for if row index is 1 then previous value is 0 but then I cannot reference previous values in that column after that anyway.

I had a look through the Power Query M documentation but didn't see anything that would suit in this case. Unfortunately our data source is not very flexible so I am unable to calculate this prior to the import to BI.

[v-janeyg-msft]

Hi, @M4dsteve 

Regarding the problem of recursion, some powerbi experts have studied it, and there is no good way. DAX and M are not easy to handle. What do you mean by inflexible data sources? If there is a lot of data, you can import it in batches. Have you tried to use the $A$ dynamic reference form to calculate the result in excel? 

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[M4dsteve · Accepted solution]

Hi @v-janeyg-msft ,

I managed to solve this with a python script that outputs the data after processing the recursive calculation. 

Turns out pandas is super powerful and it was a good learning experience 😄

Snippet of code from that column:

 # Build pdep Column
    for i in range(0, (len(support))):
        if support.loc[i, 'IsNDValue']:
            na = 0
        else:
            na = support.loc[i, 'Exposure Result mg_x']
        nbj = support.loc[i, 'n_bj']
        if i != 0:
            prev = support.loc[i - 1, 'pdep']
            support.loc[i, 'pdep'] = prev + (1 - prev) * na / (na + nbj)
        else:
            prev = 0
            support.loc[i, 'pdep'] = prev + (1 - prev) * na / (na + nbj)

Thanks for your help.

[v-janeyg-msft]

Hi, @M4dsteve 

I am very happy that you can use python to solve the problem, because dax is a data analysis language and is not suitable for calculation iteration. It is your ability to use multiple languages fluently.😆

You can mark your answer as solution.

Best Regards

Janey Guo