Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
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
Thanks for your help!
...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.
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:
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |