Forum Discussion

rodfernandez's avatar
rodfernandez
Helper I
7 years ago
Solved

Absolute Cumulative Graph

Hi I have the following data  Type PoR Amount Month A Real 5 1 A Real 4 2 A Real 5 3 A Real 8 4 A Real 9 5 A Plan 2 1 A Plan 3 2 A Plan 6 3 ...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi rodfernandez

    Create these measures instead

    ABS Real-Plan =
ABS (
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Type], 'Table'[Month] ),
            'Table'[PoR] = "Real"
        )
    )
        - CALCULATE (
            SUM ( 'Table'[Amount] ),
            FILTER (
                ALLEXCEPT ( 'Table', 'Table'[Type], 'Table'[Month] ),
                'Table'[PoR] = "Plan"
            )
        )
)

Cumulative =
SUMX (
    FILTER (
        ALL ( 'Table' ),
        'Table'[Type] = MAX ( 'Table'[Type] )
            && 'Table'[PoR] = MAX ( 'Table'[PoR] )
            && [Month] <= MAX ( [Month] )
    ),
    [ABS Real-Plan]
)

     

    Best Regards

    Maggie

v-juanli-msft's avatar
v-juanli-msft
Community Support
7 years ago

Hi rodfernandez

Create these measures instead

ABS Real-Plan =
ABS (
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Type], 'Table'[Month] ),
            'Table'[PoR] = "Real"
        )
    )
        - CALCULATE (
            SUM ( 'Table'[Amount] ),
            FILTER (
                ALLEXCEPT ( 'Table', 'Table'[Type], 'Table'[Month] ),
                'Table'[PoR] = "Plan"
            )
        )
)

Cumulative =
SUMX (
    FILTER (
        ALL ( 'Table' ),
        'Table'[Type] = MAX ( 'Table'[Type] )
            && 'Table'[PoR] = MAX ( 'Table'[PoR] )
            && [Month] <= MAX ( [Month] )
    ),
    [ABS Real-Plan]
)

 

Best Regards

Maggie