cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TrentS Frequent Visitor
Frequent Visitor

Line and Stacked Columns and Running Totals

Hi Community,

 

Hoping for some education here. What is a simple Excel COUNTIF is eluding me in PBI.

I am creating a Line and Stacked Column chart to show case status within a given time period (week end or month). I get the per week data correctly set but the line formulas/measures are not as expected.

The columns display how many cases were opened/closed per week but I want the line to show the running total with the Status "Open". Here's an example. (Week end date is referenced from another table in my source data)

Statusexample.PNG

 

The Stacked columns show (with a filter of closed for example)  5 for 'week end' 4/5/2019 and 2 for 4/12/2019. 'Week end' would be my X axis in this example.

I want the line to show a running total of Open cases.

In this example, it would show 6 for 4/5/2019 and 8 for 4/12/2019 being the running total of open cases.

I believe this can be accomplished through measures instead of new column creation but please correct me. Which COUNT functions should I be looking at here? Rows counts adding filters?

I've read the various DAX COUNT variations multiple times and I'm missing something..

Guidance is much appreciated.

Trent 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Line and Stacked Columns and Running Totals

@TrentS try following measure

 

Running Total Open Cases =
CALCULATE ( 
COUNTROWS( YourTable ), 
YourTable[Status] = "Opened",
FILTER (
ALL( YourTable[Week End Date] ),
YourTable[Week End Date] <= MAX( YourTable[ Week End Date] )
)
)




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

Proud to be a Datanaut! Kudos Help Smiley Happy
Connect with me on Linkedin. Feel free to email me with any of your BI needs.




 




3 REPLIES 3
Super User
Super User

Re: Line and Stacked Columns and Running Totals

@TrentS try following measure

 

Running Total Open Cases =
CALCULATE ( 
COUNTROWS( YourTable ), 
YourTable[Status] = "Opened",
FILTER (
ALL( YourTable[Week End Date] ),
YourTable[Week End Date] <= MAX( YourTable[ Week End Date] )
)
)




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

Proud to be a Datanaut! Kudos Help Smiley Happy
Connect with me on Linkedin. Feel free to email me with any of your BI needs.




 




TrentS Frequent Visitor
Frequent Visitor

Re: Line and Stacked Columns and Running Totals

@parry2k 

 

Thank you Parry for that speedy response. You get the solution acceptance as you delivered exactly what I requested. (I had essentially the same measure (with the exception of the last line which I will go research for future).

 

You also made me realize why the chart was not looking the way it had within Excel. The file contains all of the historical data since the inception of it and that is what was being referenced.

 

In the case I stated, it would show the current status of all cases (and again you delivered the solution) but the status changes as cases are closed out over time and new ones are opened. I need to figure out how to pull the historical data in, retain it (new table I assume), and also generate the new numbers moving forward. The last 'Week End' count generated from your measure would be the one I want to keep.

That way, in a year, I can still see that on week ending 4/12/2019 we had 8 open case and on 4/*/2020 we had 10 and so forth.

Ok, more work needed to turn this into an automated and interactive PBI dashboard!

Thanks for the eye opener!

 

Trent

Super User
Super User

Re: Line and Stacked Columns and Running Totals

@TrentS I'm glad that I was able to help, never hestitate to reach out if I can assist in any way. Cheers!!!





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

Proud to be a Datanaut! Kudos Help Smiley Happy
Connect with me on Linkedin. Feel free to email me with any of your BI needs.