Power BI
Needs VotesStacked area charts do not properly deal with missing values. When generating a stacked area chart, missing values result in white gaps.
james on 05 May 2017 10:17:41
Stacked area charts do not properly deal with missing values. When generating a stacked area chart, missing values result in white gaps. The stacked area chart function should treat absent values as 0, in order to properly render the chart.
- Comments (6)
RE: Stacked area charts do not properly deal with missing values. When generating a stacked area chart, missing values result in white gaps.
'+1 on this incredibly basic piece of functionality. Most Stacked Area Charts will look like absolute garbage in PBI because of this.
RE: Stacked area charts do not properly deal with missing values. When generating a stacked area chart, missing values result in white gaps.
Using a NVL/COALESCE (or similar for other data sources) function on the underlying data would work if there was actually a NULL in the dataset ie:
Feb 1 2018 5
Mar 1 2018 NULL (replace with 0)
Apr 1 2018 10
But often the entry will not actually be present ie:
Feb 1 2018 5
Apr 1 2018 10
Any solution would need to decide which dates to add in (just Mar 1, or Feb 2-Mar 31)?
RE: Stacked area charts do not properly deal with missing values. When generating a stacked area chart, missing values result in white gaps.
A workaround: If source data is derived for example from:
. SELECT AAA, A_DATE, A_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
I implemented this as a view in the underlying database, but there are probably other ways
RE: Stacked area charts do not properly deal with missing values. When generating a stacked area chart, missing values result in white gaps.
A tick box which converts NULL to 0 should do the trick? Easy to implement.
RE: Stacked area charts do not properly deal with missing values. When generating a stacked area chart, missing values result in white gaps.
This is an incredibly frustrating oversight.
RE: Stacked area charts do not properly deal with missing values. When generating a stacked area chart, missing values result in white gaps.
I agree. This makes the look of the stacked area weird. I can provide an example, if you guys would like to see it. :)