Stacked area chart with mergers

[IMett]

Hi everyone, 
I am stuck with a rather specific visualization problem.

I explore the budget (or sales, whatever...) of several companies over time. I would like to display them with a stacked area chart, if possible. However, there are mergers between the companies, so at some points, I want the areas to flow together.

So far I could only achieve a graph like this, however there are several problems:

- the annotations look horrible. I would like to have one annotation inside of each independent area indicating the company name instead of annotate each data point.

- It's very hard for the viewer to recognize that C and E merged in July 2020, while A and B merged in January 2021.

At least the merged companies would need to be sorted together on the Y axis, and maybe there is also a chance to use smarter color coding (of course i could specify each color manually, but that would be very troublesome, considering that in the real use case there are hundreds of companies and the user should be allowed to pick some of them in a slicer.

Does anyone have a good idea how to improve this visualization?

TMDL scripts for my example

createOrReplace

    table Budgets

        lineageTag: 0c2dc0fb-08ec-46e8-8299-2f8cb5c655eb

        measure BudgetPerTime = ```

                

                 var _currentdate = LASTDATE(Calendar[Date])

                 var _result = 

                 CALCULATE(SUM(Budgets[Budget]),Budgets[From] <= _currentdate, Budgets[To] >= _currentdate)

                 return _result

                ```

            formatString: 0

            lineageTag: c57d883d-9d25-4b61-835e-80e880c9cb0b

        column Company

            dataType: string

            lineageTag: 93fa9c76-816d-4399-8d5c-6ab954baceea

            summarizeBy: none

            sourceColumn: Company

            annotation SummarizationSetBy = Automatic

        column From

            dataType: dateTime

            formatString: Short Date

            lineageTag: 3f0bb509-0a93-4eab-90ce-68e9ff15731c

            summarizeBy: none

            sourceColumn: From

            annotation SummarizationSetBy = Automatic

            annotation UnderlyingDateTimeDataType = Date

        column To

            dataType: dateTime

            formatString: Short Date

            lineageTag: 50896cfb-beae-41a7-8380-3f0f77957362

            summarizeBy: none

            sourceColumn: To

            annotation SummarizationSetBy = Automatic

            annotation UnderlyingDateTimeDataType = Date

        column Budget

            dataType: int64

            formatString: 0

            lineageTag: ba50b328-c7ab-4a85-8d1b-02044c346d22

            summarizeBy: sum

            sourceColumn: Budget

            annotation SummarizationSetBy = Automatic

        column SID

            dataType: int64

            formatString: 0

            lineageTag: 8f497e5d-949b-4185-b54b-811da1d30da7

            summarizeBy: count

            sourceColumn: SID

            annotation SummarizationSetBy = Automatic

        partition Budgets = m

            mode: import

            source =

                    let

                        Quelle = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lc9bCoQwDAXQveRbJEmb6q+PWUXp/rcxJUNr1CgMFMqFQ26SMywwANJYHyNjDaGG0AIh6gdlOGg0lPiV6lSylP6jopJVrt6q0kJyZHI3nYzcbjPRHDirDCcpVvYCEkP3x3rq9VHl51rP84h90ahSTjK49SyH3Bqd3Hr6HZWglC8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Company = _t, From = _t, To = _t, Budget = _t, SID = _t]),

                        #"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Company", type text}, {"From", type date}, {"To", type date}, {"Budget", Int64.Type}, {"SID", Int64.Type}})

                    in

                        #"Geänderter Typ"

        changedProperty = Name

        annotation PBI_ResultType = Table

        annotation PBI_NavigationStepName = Navigation

createOrReplace

    table Calendar

        lineageTag: 26dd22b9-dfec-4268-99be-bc1449b0ec29

        column Date

            formatString: Short Date

            lineageTag: 3f70e015-ff81-445e-a5c3-4b2e19ede480

            summarizeBy: none

            isNameInferred

            sourceColumn: [Date]

            annotation SummarizationSetBy = Automatic

        partition Calendar = calculated

            mode: import

            source =

                    

                    CALENDARAUTO()

        annotation PBI_Id = da40226953204e22b45f8cd6f0625867

createOrReplace

    table Mergers

        lineageTag: 59ca12de-c94f-434a-b190-2ed4e62c9824

        column Company_original

            dataType: string

            lineageTag: 8f85fe8e-08e4-4c03-b978-c7d8704d0827

            summarizeBy: none

            sourceColumn: Company_original

            sortByColumn: Company_new

            changedProperty = SortByColumn

            annotation SummarizationSetBy = Automatic

        column Company_new

            dataType: string

            lineageTag: a007cf2d-1a03-43c0-8a4e-dee7b96c5244

            summarizeBy: none

            sourceColumn: Company_new

            annotation SummarizationSetBy = Automatic

        column MergeDate

            dataType: dateTime

            formatString: Long Date

            lineageTag: 7f3a30f3-39bb-4425-9ec5-82f55518d55e

            summarizeBy: none

            sourceColumn: MergeDate

            annotation SummarizationSetBy = Automatic

            annotation UnderlyingDateTimeDataType = Date

        partition Mergers = m

            mode: import

            source =

                    let

                        Quelle = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSAWMDQz0gMjIwMlCK1YlWcsIu7AwUcnaFipsjxF2xiccCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Company_original = _t, Company_new = _t, MergeDate = _t]),

                        #"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Company_original", type text}, {"Company_new", type text}, {"MergeDate", type date}})

                    in

                        #"Geänderter Typ"

        annotation PBI_ResultType = Table

        annotation PBI_NavigationStepName = Navigation

createOrReplace

    relationship 3d494634-3e9a-f902-7f43-1efcabac2250

        fromColumn: Budgets.Company

        toColumn: Mergers.Company_original

createOrReplace

    ref table Budgets

        measure BudgetPerTime = ```

                

                 var _currentdate = LASTDATE(Calendar[Date])

                 var _result = 

                 CALCULATE(SUM(Budgets[Budget]),Budgets[From] <= _currentdate, Budgets[To] >= _currentdate)

                 return _result

                ```

            formatString: 0

            lineageTag: c57d883d-9d25-4b61-835e-80e880c9cb0b

[v-nmadadi-msft]

Hi @IMett,
Thanks for reaching out to the Microsoft fabric community forum.

Stacked Bar chart may not be the best possible solution for what you are trying to achieve, try using Sankey chart to create the required visual.
A Sankey Chart provides a more intuitive way to represent company mergers by visually illustrating how entities merge over time. Before a merger, each company appears as an individual node, and as they combine, their flows converge into a single node, making transitions clear. This approach effectively highlights mergers, helping viewers track the evolution of companies in a more structured and engaging manner.

If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thanks and Regards

[IMett]

Hi @v-nmadadi-msft , thanks for you hint. While Sankey is certainly a good visualization in order to illustrate mergers, I don't know of a way to add a time dimension to the graph, which is the most important aspect of the task.

[v-nmadadi-msft · Accepted solution]

Hi @IMett,

Based on your clarification, I now understand why you've chosen a stacked bar chart and the value it brings to your analysis. To enhance the visual appeal and improve clarity for your viewers, here are a few suggestions you might consider:

To highlight merger events effectively within your area chart, navigate to the Analytics pane in Power BI and add a Constant Line at the specific date when the merger occurred. Customize the line’s colour, style, and label to clearly indicate the nature of the event for example, labelling it as “Company C + E Merger.” This creates a clear and immediate visual cue that helps users recognize when significant structural changes took place, enhancing the interpretability of your chart.

To visually represent mergers in a meaningful way, it’s important to apply a consistent and intuitive colour scheme. Assign a distinct colour to each individual company to clearly differentiate them before any merger. When companies merge, use a blended or transitional colour that visually combines the original colours this creates a natural visual cue indicating the merger. For example, if Company A is blue and Company B is green, the merged entity could use a teal shade. This approach helps users quickly understand both historical ownership and post-merger relationships at a glance.

I hope these suggestions help you further enhance and refine your visual for a more impactful presentation.

If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thanks and Regards