Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
ProductSite_A | Download | 5000 | |
ProductSite_A | Contact | 15000 | |
ProductSite_B | Subscription | 1000 | |
ProductSite_B | Contact | 500 | |
ProductSite_A | Contact | 3000 | |
ProductSite_B | Download | 6000 | |
ProductSite_B | Subscription | 9000 | |
ProductSite_B | Contact | 700 | |
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 |
ProductSite_A | 2 | ? | |
ProductSite_B | 2 | ? | |
ProductSite_A | 1 | ? | |
ProductSite_B | 3 | ? | |
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 | ? |
Solved! Go to 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
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
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |