Trying to do both non-stacked columns and stacked columns in a chart

Question

I am an author, and working to make a system to track my details. This item is a chart where I want to have the chart have a column for each title. Then in each column, I want to have it have it stacked with number of each edition (currently, ebook, hard cover, and paperback).

The feeding database view sums the sales by edition type, for each story, within the month, and displays a date on the first of the month.

SELECT
     CONVERT(varchar(4), YEAR(dbo.Sales.SaleWhen)) + '-' + RIGHT('00' + CONVERT(varchar(2), MONTH(dbo.Sales.SaleWhen)), 2) + '-01' AS SaleWhen, 
     dbo.Edition.Story, 
     dbo.Edition.EdType, 
     SUM(dbo.Sales.SaleCount) AS SumOfSaleCount
FROM
     dbo.Edition 
     INNER JOIN dbo.Sales ON dbo.Edition.ISBN = dbo.Sales.Edition
GROUP BY 
     YEAR(dbo.Sales.SaleWhen), 
     MONTH(dbo.Sales.SaleWhen), 
     dbo.Edition.Story, 
     dbo.Edition.EdType

It returns a table with SaleWhen, Story, EdType, and SumOfSaleCount

Result: Database Output If wanted, tab separated value of output

• SaleWhen is the X axis.
• Story is the Non-Stacked columns
• EdType is the stacked columns
• SumOfSaleCount is the values.

I have a working DataSet to access the data. The Form contains a Chart element, docked to fill the form, and is called by the MDI interface (which is in the Project). The form also has the DataSet, BindingSource, and TableAdapter.

Form Code which didn't change:

   Private Sub Reports_BG_Sales_Months_ByEditionType_Load(sender As Object, e As EventArgs) Handles MyBase.Load
       'Loads data into the 'BooksDataSet.SalesByDateMonth1_ByStoryByEdition' table. 
       Me.SalesByDateMonth1_ByStoryByEditionTableAdapter.Fill(Me.BooksDataSet.SalesByDateMonth1_ByStoryByEdition)

       LoadChart() 'Load the data to the chart
   End Sub

Function in MiscFunctions:

   Function GetDistinctSeries(dataTable As DataTable, SeriesLabel As String) As List(Of String)
        Dim LabelList As New List(Of String)()

        For Each row As DataRow In dataTable.Rows
            Dim LabelEntry As String = row(SeriesLabel).ToString()
            If Not LabelList.Contains(LabelEntry) Then
                LabelList.Add(LabelEntry)
            End If
        Next

        Return LabelList
    End Function

What I am getting is the stories showing correctly, but the entries for each month are stacking, but are stacking with only one colour, and each starting at 0, not on top of the next. This is showing only whichever edition was the tallest (the largest) for the story for that month.

    Sub LoadChart()
        Dim dataTable As DataTable = BooksDataSet.Tables("SalesByDateMonth1_ByStoryByEdition")

        ' Clear existing series
        Chart1.Series.Clear()

        ' Define series for each Story
        Dim RowXLabel = "SaleWhen" 'The Column name with the value for the X Axis (Date)
        Dim RowYLabel = "SumOfSaleCount" 'The column name with the value for the Y Axis (Integer)
        Dim StoryLabel = "Story" 'The column name with the label for the series (String, with each series having a value)
        Dim EdTypeLabel = "EdType" 'The column name with the label for the edition type (String)

        'Get all the stories into a list
        Dim Stories = MiscFunctions.GetDistinctSeries(dataTable, StoryLabel)
        'Get all the edition types used
        Dim EditionTypes = MiscFunctions.GetDistinctSeries(dataTable, EdTypeLabel)
        'Get all the dates used
        Dim DateList = MiscFunctions.GetDistinctSeries(dataTable, RowXLabel)

        ' Iterate through each story
        For Each story As String In Stories
            ' Create a series for the story
            Dim series As New Series(story)
            series.ChartType = SeriesChartType.Column
            series.Name = story ' Set the Series Title to the value of the StoryLabel

            'Get all the distinct dates (Dates are grouped by the DB View to Year, Month, Story, then Edition)
            For Each DistDate As String In DateList

                ' Get distinct edition types for the current story
                'Dim editionTypes = GetDistinctSeries(dataTable.AsEnumerable().Where(Function(row) row.Field(Of String)(StoryLabel) = story).CopyToDataTable(), EdTypeLabel)


                ' Add data points for each edition type within the story
                For Each edType As String In EditionTypes
                    ' Create a series for the edition type within the story
                    Dim edTypeSeries As New Series(edType)
                    edTypeSeries.ChartType = SeriesChartType.StackedColumn
                    edTypeSeries.Name = edType ' Set the Series Title to the value of the EdTypeLabel

                    ' Add data points for the current edition type
                    For Each row As DataRow In dataTable.Rows
                        If row(StoryLabel).ToString() = story AndAlso row(EdTypeLabel).ToString() = edType Then
                            Dim XValue As Date = row(RowXLabel)
                            Dim YValue As Integer = Convert.ToInt32(row(RowYLabel))

                            ' Add data point to the series with adjusted Y value
                            edTypeSeries.Points.AddXY(XValue, YValue)
                        End If
                    Next

                    ' Add the edition type series to the story series
                    For Each point As DataPoint In edTypeSeries.Points
                        series.Points.Add(point)
                    Next
                Next
            Next
            'series.IsValueShownAsLabel = True

            ' Add the story series to the chart
            Chart1.Series.Add(series)
        Next

        ' Show data values on each column
        'For Each series As Series In Chart1.Series
        '    series.IsValueShownAsLabel = True
        'Next
    End Sub

Displayed chart output when I had it with the stories in Column

I can get it to display the chart with stacked columns, but cannot get it to display the stories separately.

    Sub LoadChart()
        Dim dataTable As DataTable = BooksDataSet.Tables("SalesByDateMonth1_ByStoryByEdition")

        ' Clear existing series
        Chart1.Series.Clear()

        ' Define column names
        Dim RowXLabel = "SaleWhen" ' The Column name with the value for the X Axis (Date)
        Dim RowYLabel = "SumOfSaleCount" ' The column name with the value for the Y Axis (Integer)
        Dim StoryLabel = "Story" ' The column name with the label for the series (String, with each series having a value)
        Dim EdTypeLabel = "EdType" ' The column name with the label for the edition type (String)

        ' Get all distinct stories, edition types, and dates
        Dim Stories = MiscFunctions.GetDistinctSeries(dataTable, StoryLabel)
        Dim EditionTypes = MiscFunctions.GetDistinctSeries(dataTable, EdTypeLabel)
        Dim DateList = MiscFunctions.GetDistinctSeries(dataTable, RowXLabel)

        ' Initialize series for each story-edition combination
        Dim seriesDict As New Dictionary(Of String, Series)
        For Each story As String In Stories
            For Each edType As String In EditionTypes
                Dim seriesName = $"{story} - {edType}"
                Dim series As New Series(seriesName)
                series.ChartType = SeriesChartType.StackedColumn
                'series.IsValueShownAsLabel = True
                seriesDict(seriesName) = series
            Next
        Next

        ' Iterate through each story
        For Each story As String In Stories
            ' Create a separate series for each edition type within each story
            For Each edType As String In EditionTypes
                Dim seriesName = $"{story} - {edType}"
                Dim series = seriesDict(seriesName)

                ' Iterate through each date
                For Each dateStr As String In DateList
                    Dim dateValue As DateTime = DateTime.Parse(dateStr)
                    Dim sumOfSaleCount As Integer = 0

                    ' Find the corresponding row for the current story, date, and edition type
                    Dim foundRows = dataTable.Select($"{StoryLabel} = '{story}' AND {RowXLabel} = '{dateStr}' AND {EdTypeLabel} = '{edType}'")
                    If foundRows.Length > 0 Then
                        sumOfSaleCount = Convert.ToInt32(foundRows(0)(RowYLabel))
                    End If

                    ' Add the data point to the series
                    Dim point As New DataPoint
                    point.SetValueXY(dateValue, sumOfSaleCount)

                    ' Conditionally show the label only if the value is not zero
                    If sumOfSaleCount > 0 Then
                        point.Label = sumOfSaleCount.ToString()
                    Else
                        point.Label = String.Empty
                    End If

                    series.Points.Add(point)
                Next

                ' Add the series to the chart
                Chart1.Series.Add(series)
            Next
        Next

        ' Ensure x-axis is set to DateTime
        Chart1.ChartAreas(0).AxisX.IntervalType = DateTimeIntervalType.Months
        Chart1.ChartAreas(0).AxisX.LabelStyle.Format = "yyyy-MM"

        ' Add legends for each story separately
        Chart1.Legends.Clear()
        For Each story As String In Stories
            Dim legend As New Legend(story)
            legend.Docking = Docking.Top
            legend.Alignment = StringAlignment.Center
            legend.DockedToChartArea = "ChartArea1"
            legend.IsDockedInsideChartArea = True
            Chart1.Legends.Add(legend)
        Next

        ' Assign series to the correct legend
        For Each story As String In Stories
            For Each edType As String In EditionTypes
                Dim seriesName = $"{story} - {edType}"
                Chart1.Series(seriesName).Legend = story
            Next
        Next
    End Sub

Displayed chart where it stacks everything together

I would like to have the value labels shown, but not many times over for each one Like This -- which is why that part is commented out. (Figured this out... by setting the point labels)

I did look at the other posts here, many of which are old (well over 5 years), and never have a working answer. If it's just a duplicate, so be it; will consider it an answer of can't be done.

I did try ChatGPT, but it didn't give me a solution for it which didn't have it processing any better, and often worse (either it tries to also have Story in the x-axis, or makes it not work any closer, or it displays one of the 2 results above). Anyone's help would be appreciated (even if the answer is it won't work, and I can move onto the next item in my work).