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' an...
Need Help
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!

  • Vvelarde's avatar
    Vvelarde
    Community Champion
    9 years ago

    cristiannt

     

    Instead of All you can use AllSelected

    • cristiannt's avatar
      cristiannt
      Frequent Visitor
      9 years ago

      Vvelarde

       

      Thank you again mate! But with AllSelected I'm having the same result.

       

      My formula is now this one:

      Previo =
      VAR EstadoPrev = CambiosEstado[ID_Estado] - 1
      RETURN
      DIVIDE (
      CALCULATE ( ( CambiosEstado[Total_Solicitudes]) ),
      CALCULATE (
      CambiosEstado[Total_Solicitudes],
      FILTER ( ALLSELECTED(CambiosEstado), CambiosEstado[ID_Estado] = EstadoPrev )
      )
      )

       

       

       

      ID_Estado 2, 3 and 4 should have 1,00 Previo, and ID_Estado 5 should have 0,44. Now this is dividing all by 32, which is the total amount of Solicitudes.

       

      Thank you!

      • Vvelarde's avatar
        Vvelarde
        Community Champion
        9 years ago

        hi cristiannt

         

        Use This Measure:

         

        PrevioM% =
VAR EstadoPrev =
    MIN ( CambiosEstado[Estado] ) - 1
RETURN
    IF (
        HASONEVALUE ( CambiosEstado[Estado] ),
        DIVIDE (
            CALCULATE ( SUM ( CambiosEstado[Solicitudes] ) ),
            CALCULATE (
                SUM ( CambiosEstado[Solicitudes] ),
                FILTER (
                    ALLEXCEPT ( CambiosEstado; CambiosEstado[Fecha] ),
                    CambiosEstado[Estado] = EstadoPrev
                )
            )
        )
            + 0
    )