cancel
Showing results for
Did you mean:
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!

6 REPLIES 6
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)...

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

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.

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 )```
Regular Visitor

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:

Regular Visitor

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.

Microsoft

@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.

Announcements