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

Cumulative count for graph

Hello,

 

I am trying to produce a cumulative count for my data, I've attempted to use a method that I see frequently in the forums but cannot seem to get it to work.

 

See screen shot attached with attempted DAX formula, and the graph it outputs (which is clearly not cumulative).

 

cumulative = CALCULATE (COUNTROWS('Asta Outputs - Task'), FILTER (ALLSELECTED('Asta Outputs - Task'), 'Asta Outputs - Task'[StartDate] <= MAX('Asta Outputs - Task'[StartDate])))

So Im attampting to create a count of rows building up by date.

 

Any help would be appricated.

 

Attempt at Cumulative Count.PNG

Graph.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Cumulative count for graph

Hi @chris_k,

 

I think the issue may related to the NOT(ISBLANK('Asta Outputs - Task'[ActualStart])), perhaps you can try to move the blank filter into filter function.

 

Cumulative Tasks Actually Started = CALCULATE (COUNTROWS('Asta Outputs - Task'), FILTER (ALLSELECTED('Asta Outputs - Task'), 'Asta Outputs - Task'[ActualStart] <= MAX('Asta Outputs - Task'[ActualStart])&&[ActualStart]<>BLANK()))

 

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

4 REPLIES 4
Highlighted
Super User I
Super User I

Re: Cumulative count for graph

@chris_k i think the issue here is that you need to create a date table, have you done that and then link the date to the date table





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Community Support
Community Support

Re: Cumulative count for graph

Hi @chris_k,

 

Please use measure to instead the calculate column, current power bi not support dynamic calculated column based on slicer or filter.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Highlighted
Helper I
Helper I

Re: Cumulative count for graph

I initally solved this issue, and now Ive made it far more complicated.

 

I essentially have a table with two dates in it: StartDate and ActualStart. Every row has a StartDate but the ActualStart can be blank so I adapted the formula to ignore blanks. 

 

My adaptatons work perfectly if I put them in indiviual graphs but I need to overlay them on on graph. Using either date as the X axis breaks the other count (Because I think it ignores dates and just counts values)

 

Attached are the Measures and some images of the current broken graph. 

 

As you see in the data the line Cumulative Tasks Actually Started shouldnt stay flat? But tasks planned to start is perfect. If i change the X axis to ActualStart then it reverses with Actually Started working but Start Date broken.

 

Cumulative Tasks Planned To Start = CALCULATE (COUNTROWS('Asta Outputs - Task'), FILTER (ALLSELECTED('Asta Outputs - Task'), 'Asta Outputs - Task'[StartDate] <= MAX('Asta Outputs - Task'[StartDate])))
Cumulative Tasks Actually Started = CALCULATE (COUNTROWS('Asta Outputs - Task'), FILTER (ALLSELECTED('Asta Outputs - Task'), 'Asta Outputs - Task'[ActualStart] <= MAX('Asta Outputs - Task'[ActualStart])), NOT(ISBLANK('Asta Outputs - Task'[ActualStart])))

Dates Extract.PNGGraph.PNG

Thanks,

Highlighted
Community Support
Community Support

Re: Cumulative count for graph

Hi @chris_k,

 

I think the issue may related to the NOT(ISBLANK('Asta Outputs - Task'[ActualStart])), perhaps you can try to move the blank filter into filter function.

 

Cumulative Tasks Actually Started = CALCULATE (COUNTROWS('Asta Outputs - Task'), FILTER (ALLSELECTED('Asta Outputs - Task'), 'Asta Outputs - Task'[ActualStart] <= MAX('Asta Outputs - Task'[ActualStart])&&[ActualStart]<>BLANK()))

 

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

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

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors