cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rodfernandez
Helper I
Helper I

Absolute Cumulative Graph

Hi
I have the following data 

TypePoRAmountMonth
AReal51
AReal42
AReal53
AReal84
AReal95
APlan21
APlan32
APlan63
APlan34
APlan75
BReal61
BReal72
BReal73
BReal74
BReal105
BPlan51
BPlan52
BPlan73
BPlan84
BPlan85

 

And with a measure i need to create the following absolute cumulative graph

acomulado.png

the graph show the cumulative column by type and month. The cumulative column is the absolute value of Real - Plan for each type

 

Im Using this measure to create the Absolute column of Real - Plan

ABS Real-Plan = 
abs(CALCULATE ( SUM ( Table[Amount] ); Table[PoR] = "Real" )
    - CALCULATE ( SUM ( Table[Amount] ); Table[PoR] = "Plan" ))

and im using this measure for the cumulative column that i'm graphing

Cumulative = 
CALCULATE (
    SUMX (Table; [ABS Real-Plan] );
    FILTER ( ALLSELECTED ( Table[Month] ); Table[Month] <= MAX ( Table[Month] ) )


but i'm getting a strange results in the grad total on the table and in the graphs numbers. EXAMPLE for type A

table.png


Thanks

1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

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]
)

6.png

 

Best Regards

Maggie

View solution in original post

1 REPLY 1
v-juanli-msft
Community Support
Community Support

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]
)

6.png

 

Best Regards

Maggie

View solution in original post

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors