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 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
Convert DAX expression into a table
Hi all I need to convert this DAX measure Measure = CALCULATE( MAX('Table 1'[Status]), FILTER('Table 1','Table 1'[Level]=MAX('Table 1'[Level])) ) into a new table. I tried with CALCULATETABLE and MAXX but it does not work. I want to convert because the Sankey diagram I need to use does not accept measures in the axis The data I have is as follows: Table 1: Name Level Status Extract 2D plans APS High Extract 2D plans EXE Medium Extract 2D plans LIV Low Other Function APS Medium Other Function EXE Low Other Function LIV High And what I want is as follows: Name APS EXE LIV Extract 2D plans High Medium Low Other Function Medium Low High Thanks in advance.
