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:

 

ChannelWebsiteConversionSales
XingProductSite_ADownload5000
XingProductSite_AContact15000
XingProductSite_BSubscription1000
XingProductSite_BContact500
FacebookProductSite_AContact3000
FacebookProductSite_BDownload6000
FacebookProductSite_BSubscription9000
FacebookProductSite_BContact700
FacebookProductSite_CDownload1400

 

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. 

 

SourceTargetOccursSales
XingProductSite_A2?
XingProductSite_B2? 
FacebookProductSite_A1? 
FacebookProductSite_B3? 
FacebookProductSite_C1? 
ProductSite_ADownload1 ?
ProductSite_AContact2? 
ProductSite_BSubscription2? 
ProductSite_BContact2? 
ProductSite_BDownload1? 
ProductSite_CDownload1? 
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

2 Replies

  • 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?