We have accounting data table with transactions. The transaction data includes account, debit saldo and credit saldo. We also have account master data table, which includes account and type. The type defines whether the account is income account or expense account.

Income account increases in credit and decreases in debit. On the other hand, expense account increases in debit and decreases in credit.

Of course, it might happen that income account holds higher saldo in debit than in credit, but then the account is in negative side.

Reverse bookings are done when the first booking was erroneous. Therefore for income accounts debit postings must be deducted from credit postings and for expense postings credit postings from debit postings.

First we should calculate total income and then total expense.

The transaction data in Access.

The account master data table.

The simple data model.

The measures in Power BI.

tot_i = calculate(sum(Fill_z[C])-sum(Fill_z[D]), Acc_type[Type]=”I”)

tot_e = calculate(sum(Fill_z[D])-sum(Fill_z[C]), Acc_type[Type]=”E”)

The idea with measures is to CALCULATE function. First we need difference between debit and credit, or credit and debit. After the comma, comes the filtering parameter from Acc_type table. For income accounts, calculate sums of credits minus sums of debits. For expense accounts, calculate sums of debits minus sums of credits.

The results in Power BI.

The results can be checked in Access with SQL.

SELECT Sum(fill_z.c)-Sum(fill_z.d) AS Expr1

FROM fill_z

INNER JOIN acc_type ON fill_z.acc = acc_type.acc

WHERE acc_type.type = “I”

SELECT Sum(fill_z.d)-Sum(fill_z.c) AS Expr1

FROM fill_z

INNER JOIN acc_type ON fill_z.acc = acc_type.acc

WHERE acc_type.type = “E”

In this blog I will demonstrate the use of unpivot functionality in Power Query.

Here is a sales report on horizontal axis we have countries, on vertical products. Countries are divided into Europe and Asia countries. Finland and Sweden belong to Scandinavia, Germany and Spain Central Europe, China and Korea North Asia, Singapore and Indonesia South Asia. Product from P1 to P10 belong to category 1 and P11 to P20 to category 2.

Can you make any sense with data ?

The data is not so far readable for Pivot. The table should be narrow and tall.

Let’s load the data into Power Query.

The issue is still that we should have data in pivot format to analyse the data. The data should be modified before analysing.

Activate all the countries, but not the product. Then select transform – unpivot columns.

Now the data looks better, the data is readable for pivot. The table is narrow and long. The columns are product, country and volume.

Just change the column names.

Then we need to download also the master data tables for countries and products.

As the Power Query did not recognize the first row as header, press the “use first row as header” parameter in home menu.

Then the products:

This is fine.

Let’s make the relations.

The diagram view.

Pretty simple data model: two master data tables and one transaction table.

The data to countries and data are products are both many to one cardinalities. Each product and country may appear just once in master data tables, but may appear many times in data table.

A simple measure.

Results in Power Pivot.

North Asia is biggest sub region with sales. The category 2 equally twice higher than category one.

Similar results are visible also in Power BI.

Asia covers two thirds of all sales. Product category two brings more sales than one in every country.