Conditional custom column with some math

Hi cpdanielmc21 ,

Thanks for explaining, I made this, hope that's what you want:

Measure 14 = 
VAR sumcategory = CALCULATE(SUM('Table (4)'[Amount]),FILTER(ALL('Table (4)'[Category],'Table (4)'[Amount]),'Table (4)'[Category] in {1,2}))
VAR sumcategory1 = CALCULATE(SUM('Table (4)'[Amount]),FILTER(ALL('Table (4)'[Category],'Table (4)'[Amount]),'Table (4)'[Category] in {1}))
VAR countrow2 = CALCULATE(COUNTROWS('Table (4)'),FILTER(ALL('Table (4)'[Category],'Table (4)'[Amount]),'Table (4)'[Category] = 2))
RETURN IF(MAX('Table (4)'[Category]) = 3,SUM('Table (4)'[Amount]),
        IF(sumcategory < 0,
            IF(MAX('Table (4)'[Category]) = 1,0,sumcategory1 / countrow2 + SUM('Table (4)'[Amount])),
            0))

Aiolos Zhao

Hi cpdanielmc21 ,

You can modify Anonymous 's code and create a calculated column directly.

Column = 
VAR sumcategory = 
CALCULATE(
    SUM('Table (4)'[Amount]),
    FILTER(
       ALL( 'Table (4)'),
        'Table (4)'[Category] in {1,2} && 'Table (4)'[Month] = EARLIER('Table (4)'[Month] )
    )
)
VAR sumcategory1 = 
CALCULATE(
    SUM('Table (4)'[Amount]),
    FILTER(
        ALL('Table (4)'),
        'Table (4)'[Category] = 1 && 'Table (4)'[Month] = EARLIER('Table (4)'[Month] )
    )
)
VAR countrow2 = 
CALCULATE(
    COUNTROWS('Table (4)'),
    FILTER(
        ALL('Table (4)'),
        'Table (4)'[Category] = 2 && 'Table (4)'[Month] = EARLIER('Table (4)'[Month] )
    )
)
RETURN 
IF(
    [Category] = 3,
    'Table (4)'[Amount],
    IF(
        sumcategory > 0,
        0,
        SWITCH(
            [Category],
            1, 0,
            2, (sumcategory1/countrow2) + 'Table (4)'[Amount]
        )
    )
)

Best regards,
Lionel Chen

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