Skip to main content
cancel
Showing results for 
Search instead 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

Reply
KeepCalm007
Helper I
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:

 

Capture1.PNG

 

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?

 

Capture2.PNG

 

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

 

Capture3.PNG

 

Thank you all very very much in advance!

1 ACCEPTED SOLUTION
dearwatson
Responsive Resident
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

View solution in original post

1 REPLY 1
dearwatson
Responsive Resident
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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.