Stacked area chart

[bart_vermeersch]

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

[nickobat]

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.

[jagostinhoCT]

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.

J

[dgsouth99]

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

[nickobat]

Like so ...

[VermontSC]

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

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

[uzmaa]

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

[jagostinhoCT]

Not yet, unfortunately.

[dedelman_clng]

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.

[fbrossard]

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

[bart_vermeersch]

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!

[jagostinhoCT]

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

Any news on this matter?

Were you able to sort it?

[SqlJason]

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