Forum Discussion
Sankey Chart Based on multiple Categorical Columns - Sankey Levels Mixing Multiple Columns
Hi all.
I have been trying to do a Sankey visualization based only on categorical data columns (which have some SUMMARIZE() calculations with their count and groupings), but I have encountered an issue I have not observed on Forums: the different labels are colocated across columns in a somehow random way (let's say, If I want a Sankey with three levels corresponding to three columns, the first resulting Sankey level contains labels from the dataset first and part of second column, the second level has part of the second and the third; and the third has only the rmaining of the third original dataset column).
I have followed the instructions I have found, and they all point out to a DAX UNION() and concatenation of different SUMMARIZE() functions I have put in the following table. Then, I put the original Product / Operations as Source and the remaining unified fields as destination, with the Weight being the counts calculated:
Scope in Dictionaries =
UNION(
SUMMARIZE(
'Product Scope Table',
'Product Scope Table'[Product / Operations],
'Product Scope Table'[Scope],
"Count", COUNT('Product Scope Table'[Scope])
),
SUMMARIZE(
'Product Scope Table',
'Product Scope Table'[Scope],
'Product Scope Table'[Product Category],
"Count", COUNT('Product Scope Table'[Scope])
),
SUMMARIZE(
'Product Scope Table',
'Product Scope Table'[Product Category],
'Product Scope Table'[Business Unit],
"Count", COUNT('Product Scope Table'[Scope])
)
)
The result is disheartening, and even though I tried multple different approaches; the result is always the same chaos across Sankey levels mixing up the original dataset columns. Is there any approach I might be missing? I did think creating multiple levels would be more straightforward, but I have not been able to find a solution and would really appreciate any comment:
2 Replies
- Rupak_bi
Super User
Hi ATB1999
Shankey chart works based on source and sink. SO first you need to create separate summarize tables for each hierarchy with two calucalted column name may as "source" and "sink" . now the sink of your first hierarchy will ne source for second hierarchy and sink will be the next subcategory. likewise you need to create numbers of tables with same name as "source" and "sink". then you need to union those tables to get right display in sankey chart. - AnonymousNot applicable
Hi ATB1999 ,
Could you please provide sample data or link to pbix file(does not contain sensitive data)? This will help me to reproduce the problem.
Best regards,
Mengmeng Li