Sankey Chart Data model Preparation

[snehasissamal]

Hi,

I want to have a Sankey chart to display the budget Sector -> Directorate - Expenditure type i.e 2 level one

How I can create a table to get the data in proper format for Sankey chart from original data. We are reading data from Excel sheet.

Adding the actual data format, required Sankey data format, and the expected chart. Kindly help me to build the DAX query to create dynamic calculated table so that I can bind it to Sankey chart.

Thanks in advance!!

Original DataREquired data format for Sankey ChartSample Sankey Chart

Original Data

Year	Sector	Directorate	Description	Expenditure Type	Estimated Budget
2024	Sector A	Directorate AA1	Project 1	Locked	10000
2024	Sector A	Directorate AA1	Project 2	Spent	2000
2024	Sector A	Directorate AA1	Project 3	Remaining	8000
2024	Sector A	Directorate AA2	Project 4	Locked	5000
2024	Sector A	Directorate AA2	Project 5	Remaining	2000
2024	Sector B	Directorate BB1	Project 6	Locked	50000
2024	Sector B	Directorate BB1	Project 7	Spent	25000
2024	Sector B	Directorate BB1	Project 8	Remaining	25000
2024	Sector B	Directorate BB2	Project 9	Locked	100000

Required data format 

Source	Target	Budget	Level
Sector A	Directorate AA1	20000	1
Sector A	Directorate AA2	7000	1
Sector B	Directorate BB1	100000	1
Sector B	Directorate BB2	100000	1
Directorate AA1	Locked	10000	2
Directorate AA1	Spent	2000	2
Directorate AA1	Remaining	8000	2
Directorate AA2	Locked	5000	2
Directorate AA2	Remaining	2000	2
Directorate BB1	Locked	50000	2
Directorate BB1	Spent	25000	2
Directorate BB1	Remaining	25000	2
Directorate BB2	Locked	100000	2

[Anonymous]

Hi  @snehasissamal ,

You might consider using the Original Data table's [Year ] and [Sector] as slicers and then using measure to filter.

Here are the steps you can follow：

1. Create measure.

Flag =
var _selectyear=SELECTEDVALUE('Original Data'[Year])
var _selectsector=SELECTEDVALUE('Original Data'[Sector])
var _test=SELECTCOLUMNS(FILTER(ALL('Original Data'),'Original Data'[Year]=_selectyear&&'Original Data'[Sector]=_selectsector),"Test",'Original Data'[Directorate])
return
IF(
    MAX('True Table'[Sector]) in _test || MAX('True Table'[Directorate]) in _test,1,0)

2. Place [Flag]in Filters, set is=1, apply filter.

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Anonymous · Accepted solution]

Thanks for the reply from @lbendlin , please allow me to provide another insight: 

Hi  @snehasissamal ,

Here are the steps you can follow：

1. Create calculated table.

True Table=
var _table1=
SUMMARIZE('Table',[Sector],[Directorate],"Budget",
SUMX(FILTER(ALL('Table'),[Sector]=EARLIER('Table'[Sector])&&[Directorate]=EARLIER('Table'[Directorate])),[Estimated Budget]),
    "Level","1")
var _table2=
SUMMARIZE(
    'Table',[Directorate],'Table'[Expenditure Type],"Budget",
    SUMX(
        FILTER(ALL('Table'),
        [Directorate]=EARLIER('Table'[Directorate])&&
        [Expenditure Type]=EARLIER('Table'[Expenditure Type])),[Estimated Budget]),
        "Level","2")
return
UNION(
    _table1,_table2)

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[snehasissamal]

Thank you @Anonymous . This is what I was trying to get. 

In case I need a filter(Slicer) for Sector/Year, for example, if 2024 and Sector A is selected then the Sankey chart should show 2024 and Sector A data only, what will be a good approach and which table I should filter?

[snehasissamal]

Thanks lbendlin, Here is the data:

Original Data

Year	Sector	Directorate	Description	Expenditure Type	Estimated Budget
2024	Sector A	Directorate AA1	Project 1	Locked	10000
2024	Sector A	Directorate AA1	Project 2	Spent	2000
2024	Sector A	Directorate AA1	Project 3	Remaining	8000
2024	Sector A	Directorate AA2	Project 4	Locked	5000
2024	Sector A	Directorate AA2	Project 5	Remaining	2000
2024	Sector B	Directorate BB1	Project 6	Locked	50000
2024	Sector B	Directorate BB1	Project 7	Spent	25000
2024	Sector B	Directorate BB1	Project 8	Remaining	25000
2024	Sector B	Directorate BB2	Project 9	Locked	100000

Required Data Format

Source	Target	Budget	Level
Sector A	Directorate AA1	20000	1
Sector A	Directorate AA2	7000	1
Sector B	Directorate BB1	100000	1
Sector B	Directorate BB2	100000	1
Directorate AA1	Locked	10000	2
Directorate AA1	Spent	2000	2
Directorate AA1	Remaining	8000	2
Directorate AA2	Locked	5000	2
Directorate AA2	Remaining	2000	2
Directorate BB1	Locked	50000	2
Directorate BB1	Spent	25000	2
Directorate BB1	Remaining	25000	2
Directorate BB2	Locked	100000	2

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...