Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
lwall_mba
New Member

How do you accomplish this diagram? Is this Sankey?

Hello all - I have been trying to create this in Power BI, see picture attached. 

 

https://drive.google.com/open?id=0B3TWgPuEn9sYNlFHV1ZlOFJUdW8

 

How Do You Build This in Power BI?

 

All Sankey charts I used, failed because they don't automatically group categories by month (t, t+1, t+2, etc.).

 

I want the boxes to change sizes depending on the number assigned to those labels.

 

Also, the size of the arrows connecting the boxes should have the size of the numbers departing one label and connecting to other. 

 

Another important aspect is that not every label gets an output/destination. See mainly the top ones.

 

If this is possible with a Sankey diagram, how does the data have to look like?

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

Hi @lwall_mba,

 

Based on my test, I can’t complete reproduce from your screenshot.(the label A,B,C in t+2 has no link of other labels, so I can’t display them to the visual.)

 

Below is my sample:
Source tables(t1, t2, t3):

Capture.PNGCapture2.PNGCapture3.PNG
  

Formula:

 

Table = ADDCOLUMNS(
UNION(
CROSSJOIN(SELECTCOLUMNS(Sheet1,"Key",[Key]),DISTINCT(SELECTCOLUMNS(FILTER(Sheet2,[Key]="D2"||[Key]="E2"||[Key]="F2"),"Target",[Key]))),
CROSSJOIN(SELECTCOLUMNS(Sheet2,"Key",[Key]),DISTINCT(SELECTCOLUMNS(FILTER(Sheet3,[Key]="D3"||[Key]="E3"||[Key]="F3"),"Target",[Key]))),
UNION(ROW("Key","","Target","A2"),ROW("Key","","Target","B2"),ROW("Key","","Target","C2"))),
"Value",if(AND([Key]<>"",[Target]<>""),1,0))

 

Capture4.PNG

 

Create visual:

 

Capture5.PNG 


Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

3 REPLIES 3
v-shex-msft
Community Support
Community Support

Hi @lwall_mba,

 

Based on my test, I can’t complete reproduce from your screenshot.(the label A,B,C in t+2 has no link of other labels, so I can’t display them to the visual.)

 

Below is my sample:
Source tables(t1, t2, t3):

Capture.PNGCapture2.PNGCapture3.PNG
  

Formula:

 

Table = ADDCOLUMNS(
UNION(
CROSSJOIN(SELECTCOLUMNS(Sheet1,"Key",[Key]),DISTINCT(SELECTCOLUMNS(FILTER(Sheet2,[Key]="D2"||[Key]="E2"||[Key]="F2"),"Target",[Key]))),
CROSSJOIN(SELECTCOLUMNS(Sheet2,"Key",[Key]),DISTINCT(SELECTCOLUMNS(FILTER(Sheet3,[Key]="D3"||[Key]="E3"||[Key]="F3"),"Target",[Key]))),
UNION(ROW("Key","","Target","A2"),ROW("Key","","Target","B2"),ROW("Key","","Target","C2"))),
"Value",if(AND([Key]<>"",[Target]<>""),1,0))

 

Capture4.PNG

 

Create visual:

 

Capture5.PNG 


Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

@v-shex-msft  I'm trying to do something similar, but my dataset doesn't quite look like that.  I'm trying to put this sankey to work in order to visualize how a quote changes state throughout its life.

Flow_Matrix.PNG

I've calculated the first, second, third, ect. state interaction for each quote, but I can't seem to get those lined in a sankey so that it diesplays as above.  Ideally, I'd like to see the states for each "step" in the process and be able to highlight flows much like what is possible with Behavior Flow in Google Analytics.  Can you please help me with this problem?

 

Thanks!

Virtual_Ames
Employee
Employee

Yes, that  is a Sankey diagram - available at the custom visual gallery. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.