Sankey visual - prepare data, best practice

kevlin79 · ‎10-09-2018

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 would appreciate any hints/thoughts.

The data is basically structured as follows with simple data:

Journal entry number	Date	Account number	Debet amount	Credit amount
1	20180101	3000		10 000
1	20180101	1500	10 000
2	20180101	3010		16 000
2	20180101	1500	20 000
2	20180101	2600		4 000
3	20180101	3020		30 000
3	20180101	1500	30 000
4	20180101	3000		40 000
4	20180101	1500	40 000

3xxx-accounts are revenue accounts

2600 a VAT account

1500 an accounts receivable account

So my goal would be to acheive a diagram for the above showing that total revenue of 10 000 + 16 000 + 30 000 + 40 000 = 96 000 is posted in the bookkeeping with 100 000 against account 1500 and -4 000 against account 2600.

v-juanli-msft · ‎10-09-2018

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 · ‎10-10-2018

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).