Forum Discussion

c0ld's avatar
c0ld
Regular Visitor
4 years ago
Solved

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.   Bas...
General Comment
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

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's avatar
c0ld
Regular Visitor
4 years ago

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 !

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      c0ld
      Regular Visitor
      4 years ago

      Hi Xiaoxin,

       

      This is exactly what i needed !

       

      Thank you for taking the time to help out 🙂