Forum Discussion

KeepCalm007's avatar
KeepCalm007
Helper I
9 years ago
Solved

Cumulative Graph

Hi,

I have looked around the forum for advice on cumulative graphs, and tried to do one myself. I would really appreciate some pointers with getting it right!

 

This graph is supposed to show cumulative number of programs exited every month - The legend to show how much of these are failures or completions:

 

 

The resulting graph is wrong because it actually shows a double-up of the number of cumulative program exits, so the legend isn't 'color-coding' the values the way it should - in fact is it reading it as if all cumulative program exits are failures, AND ALSO completions.

 

Can someone please help me see at what stage I went wrong? And how can I fix this?

 

 

Sample of main columns I'm working with (excludes confidential items):

 

 

Thank you all very very much in advance!

  • dearwatson's avatar
    dearwatson
    9 years ago

    Hi KeepCalm

     

    I would approach this in a different way.

     

    Instead of a calculated column build a measure to count the number of programs - if each row is 1 program then:

    Programs = COUNTROWS('Test Volumes')

     

    otherwise

    Programs = DISTINCTCOUNT('Test Volumes'[ProgramID]) - something like that.

     

    now we need a cumulative total pattern that ignores the Contract Fullfilment column in the ALL statement (since we want to use that in our graph as a legend)

     

    Cumulative Total =
    CALCULATE (
        [Programs],
        FILTER ( ALLEXCEPT ( 'Test Volumes', 'Test Volumes'[Contract Fulfillment] ), 'Test Volumes'[Closed] <= MAX ( 'Test Volumes'[Closed] ) )
    )

     

    that should give you the result you need.

1 Reply

  • dearwatson's avatar
    dearwatson
    Continued Contributor
    9 years ago

    Hi KeepCalm

     

    I would approach this in a different way.

     

    Instead of a calculated column build a measure to count the number of programs - if each row is 1 program then:

    Programs = COUNTROWS('Test Volumes')

     

    otherwise

    Programs = DISTINCTCOUNT('Test Volumes'[ProgramID]) - something like that.

     

    now we need a cumulative total pattern that ignores the Contract Fullfilment column in the ALL statement (since we want to use that in our graph as a legend)

     

    Cumulative Total =
    CALCULATE (
        [Programs],
        FILTER ( ALLEXCEPT ( 'Test Volumes', 'Test Volumes'[Contract Fulfillment] ), 'Test Volumes'[Closed] <= MAX ( 'Test Volumes'[Closed] ) )
    )

     

    that should give you the result you need.