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 cumu...
  • 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.

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.