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

 

The data is basically structured as follows with simple data:

 

Journal entry numberDateAccount numberDebet amountCredit amount
1201801013000 10 000
120180101150010 000 
2201801013010 16 000
220180101150020 000 
2201801012600 4 000
3201801013020 30 000
320180101150030 000 
4201801013000 40 000
420180101150040 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.

Need Help

3 Replies

  • 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