Forum Discussion
Sankey Chart Data model Preparation
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 |
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
5 Replies
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-Forum/ba-p/963216Thanks 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 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
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?
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
