Combine 3 charts into one in Sanky

[rutujadolas]

Hello All, I am trying to create a visual where it has the sender as Senders - profit center, business line, and business unit and the recipient has recipient - profit center, business line, and business unit. 

In general, I want to see the data movement within profit centers, business lines, and business units between the sender and the recipient. I am using the Sanky chart for this.

Now, I have created three different charts on three different page

1. Sender.Profit center to recipient.profit center

2. Sender. business unit to recepient.business unit

3. sender. business line to the recipient.business line

Is it possible if I can combine all these 3 charts into one single page using sanky?

[MFelix]

Hi @rutujadolas ,

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

[MFelix · Accepted solution]

Taking into account that you cannot make the unpivot the option I see is to create a disconnected table, this can be done in Power Query or DAX.

DAX:

Add a new table with the following code:

Sankey Table = 
UNION (
    ADDCOLUMNS (
        SELECTCOLUMNS (
            'Table',
            "Sender", 'Table'[SenderA],
            "Receiver", 'Table'[RecipientA]
        ),
        "Type", "A"
    ),
    ADDCOLUMNS (
        SELECTCOLUMNS (
            'Table',
            "Sender", 'Table'[SenderB],
            "Receiver", 'Table'[RecipientB]
        ),
        "Type", "B"
    ),
    ADDCOLUMNS (
        SELECTCOLUMNS (
            'Table',
            "Sender", 'Table'[SenderC],
            "Receiver", 'Table'[RecipientC]
        ),
        "Type", "C"
    )
)

Power Query:

In Power query you need to do the following steps:

• Select the columns of the Sender / Receiver
• Unpivot table
• Split columns (this is to get a column with Sender/Receiver and another with the A, B, C)
• Group row by the type (a, b,c)
• Add a custom column with an index column

  Table.AddIndexColumn ( [Count], "Index", 1)

  • Expand the column you created with the index
  • Remove the columns you need don't need
  • Add a custom table with the attribute and the index

    [Attribute.2] & Number.ToText ([Custom.Index])

    • Pivot column by this new column
    • Remove the character on the column:

      let
          Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY/BDQAgCAN34c0SUrYg7r+GigRBHiRNryWY0dhDTCKydwgAT6gqTbbuyeWbOH4BT+4DkcUOlhx3gSx2EBnvJX5Ewylg3nwiQH9mLg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SenderA = _t, RecipientA = _t, SenderB = _t, RecipientB = _t, SenderC = _t, RecipientC = _t]),
          #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
          #"Split Column by Position" = Table.SplitColumn(#"Unpivoted Columns", "Attribute", Splitter.SplitTextByPositions({0, 1}, true), {"Attribute.1", "Attribute.2"}),
          #"Grouped Rows" = Table.Group(#"Split Column by Position", {"Attribute.2", "Attribute.1"}, {{"Count", each _, type table [Attribute.1=nullable text, Attribute.2=nullable text, Value=text]}}),
          #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn ( [Count], "Index", 1)),
          #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Value", "Index"}, {"Value", "Custom.Index"}),
          #"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom",{"Count"}),
          #"Added Custom1" = Table.AddColumn(#"Removed Columns1", "Type", each [Attribute.2] & Number.ToText ([Custom.Index])),
          #"Removed Columns2" = Table.RemoveColumns(#"Added Custom1",{"Attribute.2", "Custom.Index"}),
          #"Pivoted Column1" = Table.Pivot(#"Removed Columns2", List.Distinct(#"Removed Columns2"[Attribute.1]), "Attribute.1", "Value"),
          #"Extracted First Characters" = Table.TransformColumns(#"Pivoted Column1", {{"Type", each Text.Start(_, 1), type text}}),
          #"Changed Type" = Table.TransformColumnTypes(#"Extracted First Characters",{{"Type", type text}, {"Sender", type text}, {"Recipient", type text}})
      in
          #"Changed Type"

      Now you can use the Receiver and Sender column on the Sankey and get the other column has a slicer.

      Check PBIX file attach.