Prepare Data for Sankey Chart

[raymond]

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	Website	Conversion	Sales
Xing	ProductSite_A	Download	5000
Xing	ProductSite_A	Contact	15000
Xing	ProductSite_B	Subscription	1000
Xing	ProductSite_B	Contact	500
Facebook	ProductSite_A	Contact	3000
Facebook	ProductSite_B	Download	6000
Facebook	ProductSite_B	Subscription	9000
Facebook	ProductSite_B	Contact	700
Facebook	ProductSite_C	Download	1400

I know I have to transform the data in a way, that I get a column for source and one for target (one extra for the occurcances) and after all the value of sales needs to be calculated for this as well. Does anyone have a clou? I am freaking out on this.

The final table should look a little like this. 

Source	Target	Occurs	Sales
Xing	ProductSite_A	2	?
Xing	ProductSite_B	2	?
Facebook	ProductSite_A	1	?
Facebook	ProductSite_B	3	?
Facebook	ProductSite_C	1	?
ProductSite_A	Download	1	?
ProductSite_A	Contact	2	?
ProductSite_B	Subscription	2	?
ProductSite_B	Contact	2	?
ProductSite_B	Download	1	?
ProductSite_C	Download	1	?

[v-yulgu-msft · Accepted solution]

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]

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?