Thanks for all the input from everyone. I've been able to get the chart I want by using the outer join of desired dates and categories, and defaulting to 0 if no data is present for a date. This gives me the data needed for an accurate stacked area chart.
The choices of NULL versus 0 is definitely worthy of discussion and understanding of the differences. I agree in most cases I would not want to display 0 if it was not truly 0 reported. However in order to get a true stacked area chart, NULLs do need to be treated as 0 or the area gets filled improperly. That's where I started with this discussion.
Here's what it looks like with 0 for NULLs.
Comparing that to my original post will show the difference using 0 for Nulls makes in a stacked area chart.
Thanks again for everyone's input.
------------------------------
Steve Bolton
Sr. Business Analyst
------------------------------
Original Message:
Sent: May 03, 2022 05:57 AM
From: James Watts
Subject: Stacked Area Chart is misleading if all groups don't have values
As a general principle, differentiating between nulls, blanks and zeroes or empty strings can be very important - in which case, use a table, or create a chart specifically to do so. An area chart without values at some points should report zero though.
------------------------------
James Watts
Business Intelligence Analyst
Original Message:
Sent: May 02, 2022 11:22 AM
From: Adam Artur Boltryk
Subject: Stacked Area Chart is misleading if all groups don't have values
Hi James,
I have only one problem with your interpretation. You ASSUME that NO DATA = 0, what can be, but dont have to be true.
BLANK for me means 'I dont know', 'I have no data'. While '0' it's EXACT value. Nothing happend. I picked '0'. I have no accidents etc. I have 0 hours sick.
In this situation it can be true, but dont have to be the truth.
IMVHO it's wrong visual picked to data bucket. We dont know why we have missing data for some people.
LINE visual can not be taken for non-continuous data. Bars maybe.
Regards,
------------------------------
Adam Artur Boltryk
Business Analyst
Original Message:
Sent: May 02, 2022 06:07 AM
From: James Watts
Subject: Stacked Area Chart is misleading if all groups don't have values
Have a look at the attached PBIX
------------------------------
James Watts
Business Intelligence Analyst
Original Message:
Sent: May 02, 2022 05:41 AM
From: James Watts
Subject: Stacked Area Chart is misleading if all groups don't have values
It's a pain. What I have done in the past is to create a table of the unique dates, then a table of the unique Short Names, then join them with a full outer join to get a table with all Short Names per date. Then merge the Hours data with an inner join. Finally, create a calculation that is zero if the value is missing or the Hours if it exists.
------------------------------
James Watts
Business Intelligence Analyst
Original Message:
Sent: Apr 28, 2022 12:01 PM
From: Steve Bolton
Subject: Stacked Area Chart is misleading if all groups don't have values
Hello,
I have a table of hours by week and position code. However some weeks have only 2 positions while others have 4 positions. This is causing my stacked area chart to have odd jumps in the data. Here's an example where position "Jrny" had values in the initial weeks, then did not show up again until much later. Instead of the stacked area showing the middle weeks as no amount, it draws a line directly to the next occurance.
Is there a way to get the chart not show during a jump in data? This is what my source data looks like.
I understand the cause is that there is no Jrny record of 0 for May 8th, but how do I fix it? I tried creating a measure that if the Hours are null then 0, but that didn't work. I assume as there was really no record with null.
------------------------------
Steve Bolton
Sr. Business Analyst
------------------------------