Forum Discussion
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:
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 🙂
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
6 Replies
- amitchandak
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)
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.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 !
