Forum Discussion

Cipriano's avatar
Cipriano
Helper III
4 years ago
Solved

Recursive calculation in table using DAX

Good afternoon everyone,

 

To request your support, I am starting in Power BI, and I need to perform the calculation of the column: consumptionCalculated, this value is calculated:

 

1.- When the father does not have a value in the column: consumption and its value is dependent on the consumption of its children. For example:

 

consumoCalculado de PadreA = consumo Hijo1dePadreA + consumo Hijo2dePadreB

consumoCalculado de PadreA = 200 + 300

consumoCalculado de PadreA = 500

 

2.- When the parent or children have a value in the column: consumption,

 

consumoCalculado de PadreC = consumo

consumoCalculado de PadreC = 100

 

consumoCalculado de Hijo1dePadreB = consumo

consumoCalculado de Hijo1dePadreB = 100

 

La tabla de datos de ejemplo:

 

clavePadreclavenombreconsumoinventariosaldoconsumoCalculado
 cve001PadreA 1000800500
 cve004PadreB   200
 cve007PadreC100600500100
cve001cve002Hijo1dePadreA200  200
cve001cvd003Hijo2dePadreA300  300
cve004cve005Hijo1dePadreB100200100100
cve004cve006Hijo2dePadreB100300200100

 

In advance, I thank you for your valuable support.

Greetings, Cipriano.

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Cipriano ,

     

    Please try this code to create a calcualted column.

    consumoCalculado = 
VAR _Child =
    SUMMARIZE (
        FILTER (
            'Table',
            'Table'[nombre] <> EARLIER ( 'Table'[nombre] )
                && CONTAINSSTRING ( 'Table'[nombre], EARLIER ( 'Table'[nombre] ) )
        ),
        [nombre]
    )
VAR _SUMCHILD =
    CALCULATE (
        SUM ( 'Table'[consumo] ),
        FILTER ( 'Table', 'Table'[nombre] IN _Child )
    )
RETURN
    IF ( 'Table'[consumo] <> BLANK (), 'Table'[consumo], _SUMCHILD )

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

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

     

3 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

    Will you have situations in your data where PadreC does not have any child records?

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Cipriano ,

     

    Please try this code to create a calcualted column.

    consumoCalculado = 
VAR _Child =
    SUMMARIZE (
        FILTER (
            'Table',
            'Table'[nombre] <> EARLIER ( 'Table'[nombre] )
                && CONTAINSSTRING ( 'Table'[nombre], EARLIER ( 'Table'[nombre] ) )
        ),
        [nombre]
    )
VAR _SUMCHILD =
    CALCULATE (
        SUM ( 'Table'[consumo] ),
        FILTER ( 'Table', 'Table'[nombre] IN _Child )
    )
RETURN
    IF ( 'Table'[consumo] <> BLANK (), 'Table'[consumo], _SUMCHILD )

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

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