Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
  • 1.  Stacked Area Chart is misleading if all groups don't have values

    Bronze Contributor
    Posted Apr 28, 2022 12:01 PM
    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
    ------------------------------


  • 2.  RE: Stacked Area Chart is misleading if all groups don't have values

    Bronze Contributor
    Posted May 02, 2022 05:41 AM
    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
    ------------------------------



  • 3.  RE: Stacked Area Chart is misleading if all groups don't have values

    Bronze Contributor
    Posted May 02, 2022 06:08 AM
      |   view attached
    Have a look at the attached PBIX

    ------------------------------
    James Watts
    Business Intelligence Analyst
    ------------------------------

    Attachment(s)



  • 4.  RE: Stacked Area Chart is misleading if all groups don't have values

    Posted May 02, 2022 11:22 AM

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



  • 5.  RE: Stacked Area Chart is misleading if all groups don't have values

    Bronze Contributor
    Posted May 03, 2022 05:57 AM
    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
    ------------------------------



  • 6.  RE: Stacked Area Chart is misleading if all groups don't have values

    Bronze Contributor
    Posted May 03, 2022 01:35 PM
    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
    ------------------------------