topic Re: Stacked area chart in Desktop

Stacked area chart

Anonymous — Fri, 12 Feb 2016 15:21:22 GMT

Hi,

I want to use a stacked area chart with dates on the X-axes, but the rendering is off with crossing lines and gaps. I guess it is caused by missing null or zero values for each categroy at a certain point in time.

Is this considered to be a bug? If not, is there an easy way to add these missing datapoints?

Thanks!

Bart

Re: Stacked area chart

SqlJason — Fri, 12 Feb 2016 15:58:34 GMT

Could we use a calculation to convert the measure to zero if the value is null?

Re: Stacked area chart

fbrossard — Fri, 12 Feb 2016 17:40:43 GMT

It's not a bug.

In all cubes technologies Null values are eliminated to optimize the number of cell-set return by a query.

In most cases, null values doesn't have anay sense for analytics.

But you have to view the missing point, you have to replace null by 0. This can be done directly from your query by replacing null by 0 (not recommended depending on high number of rows) or by using a calculated measure

Re: Stacked area chart

Anonymous — Sun, 14 Feb 2016 19:20:06 GMT

Ok, too bad...

My problem is, I have no data points for certain dates in certain categories(not even null values). How can I create these missing datapoints?

cat A - 1/1/2016 - 10

cat A - 2/1/2016 - 12

cat A - 3/1/2016 - 14

cat A - 4/1/2016 - 16

cat B - 1/1/2016 - 14

cat B - 4/1/2016 - 12

In the example above I'm missing records for

cat B - 2/1/2016 - 0

cat B - 3/1/2016 - 0

Thanks!

Re: Stacked area chart

Anonymous — Mon, 15 Aug 2016 16:39:24 GMT

I am experiencing the exact same issue, as it seems.

Any news on this matter?

Were you able to sort it?

Re: Stacked area chart

uzmaa — Tue, 25 Oct 2016 17:04:44 GMT

I am facing the same issue. Were you able to find a workaround or a fix for this?

Re: Stacked area chart

Anonymous — Fri, 16 Dec 2016 17:15:24 GMT

Not yet, unfortunately.

Re: Stacked area chart

dedelman_clng — Wed, 01 Mar 2017 16:33:03 GMT

Hi all - I just encoutered this issue as well. It is a bad look when there are random white gaps just because a new "legend" started having values in the middle of the chart.

You can achieve something similar with a stacked bar chart, if your X-axis is hierarchical (e.g. Date). It gives the same general feel but certainly does not look as nice as an area chart (without gaps) would.

Re: Stacked area chart

VermontSC — Tue, 21 Nov 2017 08:29:30 GMT

Hi,

I have the same problem. A measure did not fix it, and my data is not smooth enough (per category in the legend) to use a stacked bar chart.

Vote on this idea (found, not created by me) to increase it's likelyhood of being imlemented:

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/19164877-stacked-area-charts-do-not-properly-deal-with-miss

My ugly chart (just to add to what others have already shown)

Re: Stacked area chart

nickobat — Tue, 27 Feb 2018 16:42:47 GMT

I had this problem and used the following workaround:

If source data is derived for example from:

SELECT

AAA, -- The Legend

A_DATE, -- Time series

A_VALUE -- Stacked value

FROM TABLE_A;

Then replacing it with the following resolved the problem for me:

SELECT

AAA,

A_DATE,

A_VALUE

FROM TABLE_A

UNION ALL

SELECT DISTINCT

AAA,

cj.A_DATE,

0 AS A_VALUE

FROM TABLE_A

CROSS JOIN ( SELECT DISTINCT A_DATE FROM TABLE_A ) cj ;

My problem was actually as described by dedelman_clng (new legend starts mid series), but I think it should work for scattered nulls also.

Re: Stacked area chart

nickobat — Tue, 27 Feb 2018 20:02:10 GMT

Re: Stacked area chart

dgsouth99 — Thu, 14 Jun 2018 21:33:46 GMT

Extremely clever solution. Fixed my issue. Thanks for posting.

Re: Stacked area chart

Anonymous — Thu, 25 Apr 2019 16:40:43 GMT

Hello,

I would like to try this solution with my data but I an uncertain on where should I type this.

It does not seem like a measure, nor a column.

Can you please share a screenshot on how did you set it up?

Many thanks.