cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

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!

1 ACCEPTED SOLUTION
Responsive Resident

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.

"The commonality between science and art is in trying to see profoundly - to develop strategies of seeing and showing." - Edward Tufte
Responsive Resident

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.

"The commonality between science and art is in trying to see profoundly - to develop strategies of seeing and showing." - Edward Tufte