cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## Sum previous rows in a new column

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 🙂

1 ACCEPTED SOLUTION
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
6 REPLIES 6
Regular Visitor

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

Super User

@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.

Regular Visitor

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

Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Regular Visitor

Hi Xiaoxin,

This is exactly what i needed !

Thank you for taking the time to help out 🙂

Super User

@c0ld , Try a measure like

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