Forum Discussion

kevlin79's avatar
kevlin79
Regular Visitor
7 years ago

Sankey visual - prepare data, best practice

As a newbie to powerBI I am looking into visualising bookkeeping data into a sankey diagram. mainly to visualize which accounts sum up to all revenue bookings. But I am stuck on how to proceed and wo...
Need Help
v-juanli-msft's avatar
v-juanli-msft
Community Support
7 years ago

Hi kevlin79

Based on your example dataset and article how to create sankey chart,

I create three calculated columns in your table

category of account number

account =
SWITCH (
    TRUE (),
    LEFT ( [Account number] ) = "3", "revenue account",
    [Account number] = 1500, "receivable account",
    [Account number] = 2600, "VAT account"
)


put amount of each account category from two columns into one column

amount =
SWITCH (
    TRUE (),
    LEFT ( [Account number] ) = "3", [Credit amount],
    [Account number] = 1500, [Debet amount],
    [Account number] = 2600, - [Credit amount]
)


calculate total per account category

total = CALCULATE(SUM(Sheet2[amount]),ALLEXCEPT(Sheet2,Sheet2[account]))

 

Finally add column to the visual

 

Best Regards

Maggie

 

kevlin79's avatar
kevlin79
Regular Visitor
7 years ago

hi v-juanli-msft

 

Many thanks for your answer. Believe I need to do some reading. My goal was to achieve a chart like this (manual illustration). Perhaps I need to create an all new table based on the data in the other table (a consolidated table).

 

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    Hi kevlin79

    Is your problem sloved,

    if so, could you share how you do at your convenience or mark my answer as a solution so that others can get help from this thread?

    If bot, please don't hesiate to ask me.

     

    Best Regards

    Maggie