Forum Discussion
Solved
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 o...
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
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