Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
TrentS
Helper III
Helper III

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
parry2k
Super User
Super User

@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] )
)
)


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

3 REPLIES 3
parry2k
Super User
Super User

@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] )
)
)


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@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

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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