cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jchangqg
Regular Visitor

Running total on stacked column chart - show 0 where blank or previous value by category

Hello,

 

I need help creating a running total calculation that shows 0 when there is no data or the previous value by category. Using a running total quick measure doesn't seem to work. Any ideas? Thanks for any and all help in advance!

 

 Pic1.png

6 REPLIES 6
kevhav
Continued Contributor
Continued Contributor

Hi @Eric_Zhang et al,

 

I just encountered this same question, only with the "stacked area chart" visualization.

 

I have a running total, and I want to split it by some dimension, say Color. It doesn't do what I expect (and what I think it should)...

Sample.png

 

 

In the stacked area chart, look at the "tooltip" that is displayed for 2017-11-10. On 2017-11-10, it says Red = 10, and nothing for Blue.

 

For my purposes, that is wrong. Instead, on 2017-11-10, the total should be 16 ... it should be Red = 10 plus Blue = 6 ... and they should be "stacked!" Because it's a running total...and even though there weren't any of Blue on 2017-11-10, there were 6 of Blue before 2017-11-10.

 

On 2017-10-01, you can see they are "stacked" as expected, with 3 Red + 2 Blue = 5 total. But it only works there because there were 3 Red on 2017-10-01, and 2 Blue on 2017-10-01.

 

I tried using IF(ISBLANK(X), 0, X) ... but it doesn't change anything. I tried other variations in the DAX for the "running total" measure, but I couldn't get it to work how I want.

 

Is there some way to accomplish this? 

 

Sample raw data:

 

Date Color Counter
2017-10-01 Red 1
2017-10-01 Red 1
2017-10-01 Red 1
2017-10-01 Red 1
2017-10-01 Blue 1
2017-10-01 Blue 1
2017-10-15 Red 1
2017-10-15 Red 1
2017-10-15 Red 1
2017-10-15 Red 1
2017-10-25 Red 1
2017-11-01 Blue 1
2017-11-01 Blue 1
2017-11-01 Blue 1
2017-11-01 Blue 1
2017-11-10 Red 1
2017-11-20 Blue 1
2017-11-20 Red 1
2017-11-30 Red 1
2017-11-30 Blue 1
2017-11-30 Blue 1

 

Thanks for your help!

kevhav
Continued Contributor
Continued Contributor

...Ahh, I just realized it's like we want to reshape the result set to have one row per combination of date and color...with the running total...for all dates, even if there were no "transactions" on that date in the fact table.

 

Well, that's easy. The solution seems to be to use a separate "date dimension table," with all dates; and have the running total be over the dates in dimension table.

Eric_Zhang
Microsoft
Microsoft

@jchangqg

What are your measures like? You could try

 

measure =
VAR yourmeasure = SUM(XXXXXXX) //your measure formular here
RETURN IF ( ISBLANK (yourmeasure), 0, yourmeasure )

The running total quick measure used is:

 

Patient Counter running total in Date =
CALCULATE(
SUM('Table'[Value]),
FILTER(
CALCULATETABLE(
SUMMARIZE(
'Table',
'Table'[Date]
),
ALLSELECTED('Table')
),
ISONORAFTER(
'Table'[Date],MAX('Table'[Date]),DESC
)
))

 

Ultimately we want the running total stacked column chart to look like this:

Running Total Example - Excel.PNG

Has anyone found a solution or have any suggestions? This type of visual is easy to create in Excel but still haven't been able to find a viable solution using DAX in Power BI.


@jchangqg wrote:

Has anyone found a solution or have any suggestions? This type of visual is easy to create in Excel but still haven't been able to find a viable solution using DAX in Power BI.


@jchangqg

Could you share any sample data? The sample data is preferred in plain text or in a file, a snapshot doesn't help too much.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors