Skip to main content
cancel
Showing results for 
Search instead 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

Reply
c0ld
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

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.

View solution in original post

6 REPLIES 6
c0ld
Regular Visitor

Hi @amitchandak , thanks so much for your reply.

 

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

 

 

@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

 

TPFPTNFNRecall (expected output )
00010
10000.5
00010.333

 

Thanks for your help !

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.

Hi Xiaoxin,

 

This is exactly what i needed !

 

Thank you for taking the time to help out 🙂

amitchandak
Super User
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)

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors