Forum Discussion

andreaturri27's avatar
andreaturri27
Icon for Helper I rankHelper I
4 years ago
Solved

subtracion row custom column

Hi community, 

 

i have this situation

 

i would obtain this result it's possibile? only custom column

thank you

Regards 

 

Andrea 

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi  andreaturri27 ,

    Here are the steps you can follow：

    1. In Power query. Add Column – Index Column – From 1.

    2. Create calculated column.

    min =
var _min=MINX(FILTER(ALL('Table'),'Table'[id_progetto]=EARLIER('Table'[id_progetto])),[Index])
return
IF(
    'Table'[Index]=_min ,
    [Moltiplicazione],
    BLANK())
    Sottazione =
CALCULATE(
    SUM('Table'[min]) - SUM('Table'[h_Prog]),
    FILTER(ALL('Table'),'Table'[id_progetto]=EARLIER('Table'[id_progetto])&& 'Table'[Index]<=EARLIER('Table'[Index])))

    3. Result:

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

5 Replies

  • PabloDeheza's avatar
    PabloDeheza
    Icon for Solution Sage rankSolution Sage
    4 years ago

    Hi there!

    Clould you please describe what the logic should be?

    Thanks!

     

    • andreaturri27's avatar
      andreaturri27
      Icon for Helper I rankHelper I
      4 years ago

      3000 - 240 = 2760

      2760-60 = 2700

      2700-480=2220

      group by id_progetto

      • PabloDeheza's avatar
        PabloDeheza
        Icon for Solution Sage rankSolution Sage
        4 years ago

        I solution could be this:
        Go to Power Query and add an index column starting from 1 like this:

        Then go create a new calculated column in DAX with the following code:

        SUMX (
    FILTER (
        YourTable,
        EARLIER ( YourTable[Id_progetto] ) = YourTable[Id_progetto]
            && EARLIER ( YourTable[Index] ) >= YourTable[Index]
    ),
    YourTable[h_prog]
)

        Then add a final column like this one:

        Final Column = YourTable[Moltiplicazione] -  YourTable[PreviousCreatedColumn]

        Let me know if that helps!

  • andreaturri27's avatar
    andreaturri27
    Icon for Helper I rankHelper I
    4 years ago

    hi PabloDeheza  the solution dosn't work..

     

    Pippo2 =
    SUMX (
        FILTER (
            Stampa_Ostinelli_PROG,
            EARLIER ( Stampa_Ostinelli_PROG[Id_progetto]) = Stampa_Ostinelli_PROG[Id_progetto]
                && EARLIER ( Stampa_Ostinelli_PROG[Indice]  ) >= Stampa_Ostinelli_PROG[Indice]
        ),
       Stampa_Ostinelli_PROG[h_prog]
    )
    error is
     
    EARLIER / EARLIEST refers to a previous row context that does not exist.
     
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi  andreaturri27 ,

    Here are the steps you can follow：

    1. In Power query. Add Column – Index Column – From 1.

    2. Create calculated column.

    min =
var _min=MINX(FILTER(ALL('Table'),'Table'[id_progetto]=EARLIER('Table'[id_progetto])),[Index])
return
IF(
    'Table'[Index]=_min ,
    [Moltiplicazione],
    BLANK())
    Sottazione =
CALCULATE(
    SUM('Table'[min]) - SUM('Table'[h_Prog]),
    FILTER(ALL('Table'),'Table'[id_progetto]=EARLIER('Table'[id_progetto])&& 'Table'[Index]<=EARLIER('Table'[Index])))

    3. Result:

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly