Fill down the values in a calculated column

[kumsha1]

Hi,

How can i fill down the values of Tonnes Line?. I have asked this Q before with no luck, hence asking again...TIA

[v-deddai1-msft · Accepted solution]

Hi @kumsha1 ，

For calculated column, you can create a new column for it:

New PROGRESSIVE_TONNES = 
VAR LastNonBlankIndex =
    CALCULATE (
        LASTNONBLANK ( TRANSHIPPER_DETAILS[Index], 1 ),
        FILTER (
            ALL ( TRANSHIPPER_DETAILS ),
            TRANSHIPPER_DETAILS[Index] <= EARLIER ( TRANSHIPPER_DETAILS[Index] )
                && NOT ( ISBLANK ( TRANSHIPPER_DETAILS[PROGRESSIVE_TONNES] ) )
        )
    )
RETURN
    CALCULATE (
        SUM ( TRANSHIPPER_DETAILS[Value] ),
        FILTER ( ALL ( TRANSHIPPER_DETAILS ), TRANSHIPPER_DETAILS[Index] = LastNonBlankIndex )
    )

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

Best Regards,

Dedmon Dai

[amitchandak]

@kumsha1 , if they are column you have an option in power query same as excel to fill down the values

refer:https://www.excelcampus.com/powerquery/fill-down-blank-null-cells-power-query/

https://docs.microsoft.com/en-us/power-query/fill-values-column

[kumsha1]

Hi @amitchandak ,

Its required in a calculated column/measure then i have a requirement to cal. earlier values of this Tonne Line and use this in other calculations.

RunningMax(Tonnes Line) - Earlier(Tonnes Lines)

[kumsha1]

Below is the required output.

[mahoneypat]

Please provide the expression for the Tonnes Line measure.

Regards,

Pat

[kumsha1]

Hi @mahoneypat ,

Below is the formula, i have this as calculated column/measure.

TONNES_LINE = IF(ISBLANK([PROGRESSIVE_TONNES]) || [PROGRESSIVE_TONNES]=0,TRANSHIPPER_DETAILS[PROGRESSIVE_TON_START],TRANSHIPPER_DETAILS[PROGRESSIVE_TONNES])

Tonnes Line = IF(ISBLANK([Progressive Tonnes]) || [Progressive Tonnes]=0,[Progressive Ton Start],[Progressive Tonnes])