Sum previous rows in a new column

[c0ld]

Hello PowerBI gurus,

I am trying to visualise model performance metrics in line graphs so I have created 4 columns which capture the TP, FP, TN and FN predictions that the model has made.

Based on those columns I would like to calculate the Precision, Recall and F1 score.

I have tried to calculate the Recall= TP / (TP + FN ) with the following DAX formula:

Recall= DIVIDE('Table'[TP] , ('Table'[TP] + 'Table'[FN), 0)

However, this is not the desired output. What i am trying to achieve is :

TP / (TP + FN)

2 / (2 + 4) = 2/6 = 0.33 and at the one above it needs to be:

1 / (1 + 4) = 1/5 = 0.2 etc.

I need to do this for the precision messaure too which is: TP / ( TP + FP ) and this needs to be calculated per row level.

I have tried using SUMX too, without much success.

If anyone could help, it would be much appreciated 🙂

[c0ld]

Hi @amitchandak , thanks so much for your reply.

I have tried your suggested solution but it doesnt seem to work

[amitchandak]

@c0ld , Please try as measure and use in visual .

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[c0ld]

Hi @amitchandak , thanks for your suggestion.

I have tried using a measure and that doesnt work as i cant seem to represent the change ( if the model is improving ) with a line graph. The measure just produced a single calculation ie a dot on the plot.

Here is the sample of the data:

Recall should be: TP / (TP + FN) which is calulated based on previous values:

row 1: 0 / (0+1) = 0/1 = 0

row 2: 1 / (1 + 1 ) = 1/2 = 0.5

row 3: 1/( 1 +2) = 1/3 = 0.333

TP	FP	TN	FN	Recall (expected output )
0	0	0	1	0
1	0	0	0	0.5
0	0	0	1	0.333

Thanks for your help !

[Anonymous · Accepted solution]

HI @c0ld,

Account to your description, it sounds like a cumulative calculation between two fields. Do any Index fields exist in your tables?  If that is the case, you can refer to the following DAX formula to get results.

Measure:

formula =
VAR currIndex =
    MAX ( Table[Index] )
VAR rollTP =
    CALCULATE (
        SUM ( Table[TP] ),
        FILTER ( ALLSELECTED ( Table ), [Index] <= currIndex )
    )
VAR rollFN =
    CALCULATE (
        SUM ( Table[FN] ),
        FILTER ( ALLSELECTED ( Table ), [Index] <= currIndex )
    )
RETURN
    DIVIDE ( rollTP, rollTP + rollFN )

Calculated column:

formula =
VAR rollTP =
    CALCULATE (
        SUM ( Table[TP] ),
        FILTER ( Table, [Index] <= EARLIER ( Table[Index] ) )
    )
VAR rollFN =
    CALCULATE (
        SUM ( Table[FN] ),
        FILTER ( Table, [Index] <= EARLIER ( Table[Index] ) )
    )
RETURN
    DIVIDE ( rollTP, rollTP + rollFN )

Regards,

Xiaoxin Sheng

[c0ld]

Hi Xiaoxin,

This is exactly what i needed !

Thank you for taking the time to help out 🙂

[amitchandak]

@c0ld , Try a measure like

Recall= DIVIDE(sum('Table'[TP_Stg_1]) , sumx('Table','Table'[TP_Stg_1] + 'Table'[FN_Stg_1]), 0)