Forum Discussion

cristiannt's avatar
cristiannt
Frequent Visitor
9 years ago

Percentage from previous row

Hey! How you doing guys?

 

I'd like to know the calculation for a measure that should have the following behaviour:

It should calculate the percentage between the actual number of 'Solicitudes' and the amount of 'Solicitudes' that the previous row had.

The table should show as following:

 

Estado - Solicitudes - %From previous

1             20                0

2             20                100

3             10                50

4             5                  50

 

Is there any way from DAX to get this done?

 

Thank you!

Need Help

9 Replies

  • Vvelarde's avatar
    Vvelarde
    Community Champion
    9 years ago

    cristiannt

     

    Using your sample data you can use a calculated column:

     

     

    Previo =
VAR EstadoPrev = Table1[Estado] - 1
RETURN
    DIVIDE (
        CALCULATE ( VALUES ( Table1[Solicitudes] ) ),
        CALCULATE (
            VALUES ( Table1[Solicitudes] ),
            FILTER ( ALL ( Table1 ), Table1[Estado] = EstadoPrev )
        )
    )

     

     Or you can use the next measure:

     

    PrevioM =
VAR EstadoPrev =
    MIN ( Table1[Estado] ) - 1
RETURN
    IF (
        HASONEVALUE ( Table1[Estado] ),
        DIVIDE (
            CALCULATE ( SUM ( Table1[Solicitudes] ) ),
            CALCULATE (
                SUM ( Table1[Solicitudes] ),
                FILTER ( ALL ( Table1 ), Table1[Estado] = EstadoPrev )
            )
        )
            + 0
    )

     

     

  • tringuyenminh92's avatar
    tringuyenminh92
    Memorable Member
    9 years ago

    Hi cristiannt,

     

    • Create calculated column to compute previous value:

     

    Prev = CALCULATE(SUM(data[Solicitudes]),FILTER(data,data[Estado]=EARLIER(data[Estado])-1))

     

    • Create calculated column to compute %
    % = DIVIDE(data[Solicitudes],data[Prev])

     

     

    • cristiannt's avatar
      cristiannt
      Frequent Visitor
      9 years ago

      Thank you all for your responses!

      Hey, tringuyenminh92 I'm getting the following error: "EARLIER/EARLIEST refers to an earlier row context which doesn't exist." Could you please support please?

       

      VvelardeThis one is ok, but I have a date filter, when I apply it, the calculation is dividing by ALL Solicitudes instead of the amount of Solicitudes that actually have with that filter. Is there a way to apply this? Thank you again guys!