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
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
Employee
Employee

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