Forum Discussion

raymond's avatar
raymond
Post Patron
7 years ago
Solved

Prepare Data for Sankey Chart

Hello everyone,   we all love sankey charts. I want to draw a simpel sankey. It turns out the data prep isnt that simple after all. Can you help?  The incoming data looks like this:   Channel...
Need Help
Tips and Tricks
  • v-yulgu-msft's avatar
    v-yulgu-msft
    7 years ago

    Hi raymond,

     

    You could create a calculated table with below formula:

    Table =
UNION (
    SUMMARIZE (
        SELECTCOLUMNS ( Data, "Source", Data[Channel], "Target", Data[Website] ),
        [Source],
        [Target],
        "Occurs", COUNT ( Data[Sales] ),
        "Sales", SUM ( Data[Sales] )
    ),
    SUMMARIZE (
        SELECTCOLUMNS ( Data, "Source", Data[Website], "Target", Data[Conversion] ),
        [Source],
        [Target],
        "Occurs", COUNT ( Data[Sales] ),
        "Sales", SUM ( Data[Sales] )
    )
)

     

    Best regards,

    Yuliana Gu

v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
7 years ago

Hi raymond,

 

You could create a calculated table with below formula:

Table =
UNION (
    SUMMARIZE (
        SELECTCOLUMNS ( Data, "Source", Data[Channel], "Target", Data[Website] ),
        [Source],
        [Target],
        "Occurs", COUNT ( Data[Sales] ),
        "Sales", SUM ( Data[Sales] )
    ),
    SUMMARIZE (
        SELECTCOLUMNS ( Data, "Source", Data[Website], "Target", Data[Conversion] ),
        [Source],
        [Target],
        "Occurs", COUNT ( Data[Sales] ),
        "Sales", SUM ( Data[Sales] )
    )
)

 

Best regards,

Yuliana Gu

raymond's avatar
raymond
Post Patron
7 years ago

Hi v-yulgu-msft that works pretty good I have to say. The Sum of Sales needs to be divided by 2 though otherwise you would double the amount of sales. 

 

Another question: is there a way to do this in power query as well. I was thinking it might cause some performance issues if I am using a calculated table. Is my concern ligitimate?