You might have seen three dimensional Excel formulas.

Product one.

Product two.

Product three.

Each worksheet holds sales data by sales region and by month.

Each worksheet presents sales data per product. The reports above cover the products from one to three.

There are three dimensions: month, sales area and product. Just two dimensions, month and sales area, were presented in one worksheet.

You can create SUM functions across the three worksheets.

Total sales for North area or total sales for the month August can be calculated manually. This is not convenient for ad hoc queries.

Better way would be to have data in pivot format. Then month, region, product and sales amount should be separate columns.

For example:

Month  region   product              sales

Jan        North    P1                        23

And so on.

Add a column for the product, P1 in the sheet above, in all the three worksheets.

Take data into power query per each worksheet.

Activate the columns with header, meaning the sales areas and press right mouse button. Select unpivot only selected columns.

I added names for new column names  month, product, area and sales.

Repeat this for each worksheet P1, P2 and P3, that the sheets have the same column names in the same order.

Select append queries. The idea is to combine all the queries as one long query.

Press ok.

Import the data into data model. Create a pivot on top of data model.

The August sales is 197.

Sales for North area is 587.

When we have power query and power pivot, we can forget three dimensional worksheets.

In this example, I did not write even any DAX. It is possible to have self made measures with DAX, but it just was not needed to count total sales for August and North area.

A good thing is that the same process works also in Power BI.

My Power BI is in Finnish, but the idea is the same as in Excel.

In Power BI Aug sales is 197.

The same results in Power BI.

When a process exists in power query, you can use that both in Excel and in Power BI.

If three dimensional Excel worksheets were once used, nowadays are power query and power pivot very convenient. As said, you can use either Excel or Power BI.

You have simple sales data of sales data and product data. Sales data includes following fields: ID, product, region, rep and sales. The product data includes these fields: product, costs, price and size. The sales data is indexed by id, a separate running number. Product data is indexed by product which needs to be unique.

We need to have a sales report per region and per product size ?

The data was stored in MS Access.

The product master data.

A part of the sales transaction data.

A data model is created in Excel.

Let’s create also a measure to sum up the sales.

Instead of creating a Power Pivot on top of data model, we can still extract data with CUBEMEMBER and CUBEVALUE functions.

First, make a two-dimensional matrix. In horizontal axis we have different regions, vertically we have product size. We should fill the table with correct values. Like how many pieces of small product were sold in north region ?

Let’s start in the cell C3. We want to extract the region attributes. The sentence in C3 is =CUBEMEMBER(“ThisWorkbookDataModel”;”[Sales].[Region].[North]”) .

First, we define the data model. It is the data model under the Excel worksheet. Write “ThisWorkbookDataModel”. Secondly, we define the table. We have just two tables in our data model. We need to select the sales table as there we have regions north and south. After the table definition, select the field region and lastly, the value for region, north.

Repeat the same in D3, but now the last argument is south instead of north.

The vertical axis and the size values are created with the same logic.

Size dimension is in prod table. The arguments are datamodel, table, column and column value.

As we want to report the number of pieces sold, we have created the S_sale measure. The is the measure we would use in Power Pivot, and it is the measure we also use with cube functions.

The sentence in C2 is =CUBEMEMBER(“ThisWorkbookDataModel”;”[Measures].[S_sale]”) . The first argument with CUBEMEMBER is the data model. As we use the data model under Excel workbook, just select of type “ThisWorkbookDataModel”. The second argument is the measure itself. Select of write “[Measures], press dot, and you will have list of measures. Select the S_sale.

Now we can extract the values from data model.

For the CUBEVALUE we need show the data model, the measure and the dimensions. The data model is “ThisWorkbookDataModel”. The absolute and semi absolute references are just to make it easy to copy the formula. Show the product size, B4, region, C3, and the measure, C2.

The extracted values.

Still, we will create same report in Power BI.

Results are parallel.

Also a query was  made in Access.

SELECT sales.region, prod.size, SUM(sales.sales) AS Sale

FROM sales INNER JOIN prod ON sales.product = prod.product

GROUP BY sales.region, prod.size;

This is the SQL script.

The CUBEMEMBER and CUBEVALUE functions can withdraw the data from the Excel data model. If you don’t want to build Power Pivot or Power BI, you can simply use cube-functions. Of course, the result is not so graphical as with Power BI.  

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.

Maven Analytics published the data about 2015 New Year’s Eve resolutions tweets. The data can be

found here: https://www.mavenanalytics.io/data-playground?page=1&pageSize=5 . Thanks for Maven Analytics for providing the data.

Also sample questions were included.

1. What is the most popular resolution category? Least popular?
2. Which resolution category was retweeted the most? Least?
3. Using the tweet_created field, and rounding to the nearest hour, what was the most popular hour of day to tweet? How many resolutions were tweeted?
4. Using a map visual, what U.S. State tweeted the highest number of NYE resolutions?

The first, I downloaded the data to MS Access. Decimal symbol was changed to dot.

Reporting was done in Power BI. To add the hours in Power Query, activate the tweed_created column and select add column – time – hour. Hour information is needed in question number three.

Now the hour column appeared.

Let’s create the needed measurements.

Cat = count(New_years_resolutions[tweet_category])

The cat measure counts simply the number of tweet_category items.

FullHour = count(New_years_resolutions[Hour])

This measure counts the full hours.

Retw = count(New_years_resolutions[retweet_count])

The measure counting retweets.

States = count(New_years_resolutions[tweet_state])

This measure counts where tweets are coming from.

To double-check the values in Access.

SELECT COUNT(tweet_state), tweet_state

FROM New_years_resolutions

GROUP BY tweet_state

ORDER BY COUNT(tweet_state) DESC

;

California is leading, New York is the second.

Map used here is GlobeMap. The field tweet_state and measure states were selected.

1. What is the most popular resolution category? Least popular?

Personal growth, philantrophic.

2. Which resolution category was retweeted the most? Least?

Personal growth, time management.

3. Using the tweet_created field, and rounding to the nearest hour, what was the most popular hour of day to tweet? How many resolutions were tweeted?

9, 505.

4. Using a map visual, what U.S. State tweeted the highest number of NYE resolutions?

California.

You have accounting data. Both accounts 1000 and 2000 are revenue accounts. Each posting is a sales order. Debit postings are correction postings.

A controller is interested to know how many sales orders have been made valuing more than 1000 units. As the sales orders higher than 1000 are high value sales orders.

How many percent of the total sales, the sales order more than 1000, cover ?

Correction postings should be removed when calculating the total sales.

A sales order for 1067 was originally posted for account 2000 and cost center C2. That posting has been reversed and a correct posting was assigned to account 2000 and cost center C1. There is only one sales order for 1067.

Let’s create a table in MySQL.

The fields are:

ID running number primary key to define each record individually.

ACC bookkeeping account.

CC cost center.

DEB debit postings.

CRE credit posting.

CREATE TABLE excel_demo.onek(

id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,

acc TEXT,

cc TEXT,

deb INTEGER,

cre INTEGER

);

Before importing data you need to fill zeros to empty debit and credit cells.

Activate the value fields, take home – find & select – go to special.

Select blanks. Ok.

Enter zero and press control + enter.

All the empty value fields are populated by zero.

Now we can import the CSV-file to the MySQL table.

Map the correct source and destination columns.

Everything went fine. Data amount was small.

Check that the table has been populated with values.

ID field has values automatically. They were not in flat file.

Connect to the MySQL database.

Select get data – from database – from MySQL database

Press ok.

Transform data.

Data types are ok. Table name was shortened to onek.

Close & Load – Close & Load to

Only create connection and add this data to the data model.

=CALCULATE(count(onek[Cre]),FILTER(onek,[Cre]>1000))-CALCULATE(count(onek[Deb]),FILTER(onek,[deb]>1000))

The measure is counting records Cre-fields in onek table where Cre-fields have value more than 1000. Corresponding debit values are decreased from credit values. As 1000 and  2000 are revenue accounts, they get increased in credit side.

Add measure.

=CALCULATE(SUM(onek[Cre]),FILTER(onek,[Cre]>1000))-CALCULATE(SUM(onek[Deb]),FILTER(onek,[deb]>1000))

Total_val counts the sum of the sales order more than 1000 units.

=sum(onek[cre])-sum(onek[deb])

Sum_val counts simply the sum of credit postings minus sum of debit postings.

To count how many percent of value are the sales orders exceeding 1000:

Now we create the pivot itself.

Create pivot table based on data model.

Cost centers are on vertical and accounts on horizontal axis. No sales order above 1000 in cost center C2, all of them are in C1. Altogether four sales orders two sits in account 1000 and two in 2000.

Now to calculate the total value of sales order exceeding 1000 units.

In the account 1000 the sales order exceeding 1000 cover more than a third of sales in value. The proportion of large sales orders are considerably lower in account 2000.

All the sales order more than 1000 units are in cost center CC1 and none in CC2.

We can check same in Power BI.

Get data from MySQL database.

Similar to Excel.

Transform data.

Again similar to Excel.

DAX is similar to Excel.

In cost center C2 there are two sales lines exceeding 1000 in both accounts 1000 and 2000.

The other measures behave similarly in Power BI. I am not creating all the measures in Power BI, but they all work equally.

Let’s still check something from database, that the number are correct.

SELECT

((SELECT COUNT(excel_demo.onek.cre)

FROM excel_demo.onek

WHERE excel_demo.onek.cre > 1000)

–

(SELECT COUNT(excel_demo.onek.deb)

FROM excel_demo.onek

WHERE excel_demo.onek.deb > 1000)) AS total

;

The total number of sales order exceeding 1000 is four.

SELECT 

((SELECT SUM(excel_demo.onek.cre)

FROM excel_demo.onek)

–

(SELECT  SUM(excel_demo.onek.deb)

FROM excel_demo.onek)) AS value

;

This SQL count the total sales. The 16282 ties with Excel.

In this blog page I will show how I get reporting from MySQL database.

It is possible that there are other ways to get reporting too. This is just how I did it.

To start with, you have a simple sales report.

The dimensions are running ID number, material code, sales region and sales volume.

We could simply use the Excel as source of data for our data model. Instead of copy pasting the data to Excel, we use MySQL database.

Create a database and a table corresponding to data in the report.

Right-click the table and select table data import wizard.

Select the database table.

The fields in the file are mapped with fields in the empty table.

Now the table is populated. A good thing with SQL database, is that we can check the database with an SQL script. Note, that the database is called excel_demo.

Now we should have connection with database and Excel to get reporting done in Excel.

https://dev.mysql.com/downloads/connector/odbc/

I downloaded here the open database connector.

This is how the page looked like for me.

Search for ODBC data sources.

Press add.

Add the excel_demo database.

Select MySQL ANSI driver and press finish.

Data source is our database. TCP/IP and port are fixed values.

Press test.

The test was successful.

Excel_demo appeared to the list.

Now we can open Excel and start working on reporting side.

In Excel take data – get data – from other sources – from ODBC.

Select excel_demo from the dropdown list. Click OK.

Select database from left hand side. Enter the user name and password. Press connect.

All the available databases in MySQL are visible. We are interested in excel_demo.

Press transform data.

Check the data and data types for fields.

Close & Load – Close & Load To

Only create connection and add this data to the data model.

Create a measurement to sum up the sales volumes.

Sales per region.

Let’s check the data in MySQL.

The values match.

Another option is to use data directly from MySQL database without ODBC.

Select get data – from database – from MySQL dataset.

Localhost as server and excel_demo as database.

Transfrom data.

Close & Load – Close & Load to.

Only create connection and add this data to the data model.

Now the table name is database name + table name.

Creating a measure to sum up the sales volume.

The process is pretty similar in Power BI side, too.

Connection between Excel/Power BI and MySQL can be done via both ODBC and directly to database.

An advantage with MySQL is that you can paste data with CSV-files to MySQL tables. When you have the data in MySQL, the data can be manipulated and reviewed with SQL script. Still, the reporting can be done in Excel or in Power BI.

After the connection is in place, we can briefly analyse data.

Even though the sales were divided quite evenly between the regions, there are differences per product. None of the products are sold evenly between the regions. Also volumes per per product are varying between 9 and 45.

This blog post is an extension to my earlier blog post about accounting data in BI reporting.

Here is some accounting data. Accounts starting with three are income accounts increasing value in credit and accounts starting with four cost accounts increasing value in debit side.

The dimensions are account, cost center, date, credit value and debit value.

It would be useful to see profit and loss dynamically per cost center or per time dimension like month.

Create sum measure to count debit balance.

Summing the credit side.

These measures do not take yet correction postings into consideration. Account 3000 should be 2282 minus 250.

Sum debit total is sum debit minus sum credit.

Sum credit total is sum credit minus sum debit.

Now the balance for 3000 is 2032 in credit, but -2032 in debit should be removed.

Create an if sentence. IF sentence in DAX is pretty similar as in traditional Excel. If sum debit minus sum credit is positive, then count normally sum debit minus sum credit. If sum debit minus sum credit is negative, then the formula returns zero value. Separator is comma not semicolon.

The same operation for credit side as for the debit.

Now the results are as they should be.

The selections.

Adding the cost center.

This looks better but cost center totals are missing.

Then select subtotals – show all subtotals at bottom of group.

The highest profit was made in cost center S2.

Same measures can be created to BI also.

For me Excel work better in this case.

In this blog I will demo, how you can take reports in Excel or in Power BI, when the data is in a self made database. The database is simplified just to demo how you can report the data with Excel.

The editor I am using is SQLite3.

The table “sales” includes sales data, product and volume. The sales table is a transaction table.

The product table includes basic data of the product. This is like product master data.

In order to get SQLlite connected with Excel and Power BI, you need to download the connector from http://www.ch-werner.de/sqliteodbc and there sqliteodbc_w64.exe.

In ODBC admin press add.

Take the SQLite3 ODBC driver and press finish.

Browse your database and give a name.

Press OK.

In Excel take data – get data – from other source – from ODBC.

Open up the drop down list and select sales_demo.

Press ok.

Select windows and use my currect credentials. Press connect.

Select multiple items and select both the tables. Then press transform data.

Price in prod table was changed to be integer.

Also the sales volume is the data type of integer.

The data in Power Query is same as in SQLite3, as it should be.

Close & load – close & load to.

Only create connection and add this data to the Data model.

Take Power Pivot and Datamodel.

Diagram view.

Create a minor data model.

Take pivot table that takes the data from data model.

Use the Data model as the basis for the Pivot.

Let’s create a measure to sum the sales.

Select size as rows and sum of sales as values.

The total 121 is divided 101 large products and 20 small ones.

To calculate the sales in money, we will use SUMX and RELATED functions.

The formula is here: =sumx(sales,sales[vol]*RELATED(prod[price]))

Results in Excel.

The same process in Power BI:

Get ODBC, then connect.

Sales_demo press ok.

Connect.

Tick the tables and press transform data.

Change data type for price as integer.

Also change data type for vol to integer.

Close & apply – close & apply.

Power BI created the relation automatically, unlike Excel.

Create measure for summing the sales.

Create another measure just like in Excel.

Results in Power BI.

Doublecheck the results from database directly.

Sales volume by size.

Sales in money by size.

If you have data in a self-made database, it is possible in some cases to use Excel or Power BI for reporting.  Connector is needed but in this case, the connector was available on line. The same connector work for both Excel and Power BI.

Even though SQLite is not listed in Excel or in Power BI as a source of data, but via ODBC you can access SQLite database.

Once more, Power BI visualize the data instead of writing SQL scripts in editor even though the results are same both in database and in Power BI.

A sales report is downloaded from ERP. The dimensions are sales date, product and sales volume.

Does the weekday play a part in sales ? Are the sales for a product different between different weekdays ? Is one day in week more favourable for sales than another one ?

We can do this in several ways.

1. Traditional pivot

If we use traditional Pivot which sits on top of Excel range, we just add weekday next to sales volume. Weekday is defined by the date.

Earlier in my blog I was presenting the IF-sentence which returns you a weekday based on the WEEKDAY function.

=IF(WEEKDAY(a2)=1;”Sunday”;IF(WEEKDAY(a2)=2;”Monday”;IF(WEEKDAY(a2)=3;”Tuesday”;IF(WEEKDAY(a2)=4;”Wednesday”;IF(WEEKDAY(a2)=5;”Thursday”;IF(WEEKDAY(a2)=6;”Friday”;IF(WEEKDAY(a2)=7;”Saturday”)))))))

Create a Pivot based on the range.

Pivot selections.

The results. Weekend is a good time for sales. Friday is still ok, but Monday is really quite day.

2. The weekdays in Power query

However, we can do the same in business intelligence side.

First, we need to download the file to data model.

Transform data.

Close & Load To

Change the date column to date data type.

Add column – date – day – name of day.

Errors appeared.

Select the query options.

Change to Finnish.

Close & Load.

Now the days are ok, at least with me.

Select Add column – custom column.

The formula:

Date.DayOfWeekName([Date])

The columns are the same.

Create a pivot based on the data model.

Selections.

Results not yet sorted.

Different selections.

The same results.

3. Weekday in Power Pivot

Add new column with script below.

=if(WEEKDAY([Date];1)=1;”Sunday”;if(weekday([Date];1)=2;”Monday”; if(weekday([Date];1)=3;”Tuesday”; if(weekday([Date];1)=4;”Wednesday”; if(weekday([Date];1)=5;”Thursday”; if(weekday([Date];1)=6;”Friday”; if(weekday([Date];1)=7;”Saturday”;”Else”)))))))

Selection criterias.

Results in English.

4. Let’s see how the weekdays get reported in Power BI. The dates are in a separate table.

File – get data – text/CSV. To load the flat file.

Press transform data.

The table name was changed.

Select close & apply – close & apply.

Upload the data again, but now upload just the dates. Delete the product and sales volume data by home – remove columns.

Right click the date column header and select remove duplicates.

Select add column – date – day – name of day.

Weekdays appear in Finnish.

If you want to modify the day names, there is also another option.

Select add column – day – day of week.

Day of week returns a number. 6 is Sunday, 0 is Monday, 1 is Tuesday and so on.

Then select add column –  conditional Column.

Fill the IF-sentences via graphical interface. If Day of week (number) equals to 6 then Day short column holds the value Sun and so on.

You can name the days in your own way, I have used here the shortened values.

Close & apply.

Create a simple data model.

Cardinality is one in master data table and many in transaction table.

Create a measure to count the sales.

Sunday is the best sales day, Tuesday the worst. This is in line with Excel reports.

There are many ways to report sales by weekday. Power BI offers graphical tools for several purposes. Also having a data model with transaction table and master data table is useful. Among the options I have demonstrated, I vote for Power BI.

In this blog I present how to report accounting data in Power Pivot and Power BI.

Accounts 500 and 501 are revenue accounts and they get increased in credit side. However, some mistakes were corrected with debit postings.

When reporting the revenue, the debits should be deducted from credit.

The total revenue is 380.

However, if you want to report dynamically by month or by account, it’s better to call up Power Pivot and Power BI.

Get the data to the Power Query.

Transform data.

As the date is in Finnish format, take options and file – settings – query options.

Change regional settings to Finnish.

Change data type for date column as date. Data type for account was changed to text as we have no plans to execute any calculations based on account number. Debit and credit should be number type because they are used in measures.

File – Close & Load to…

Only create connection and add this data to the data model.

A measure to sum up the debit postings.

Another measure for the credits.

The total measure to count the net postings, meaning credits minus debits.

Select totals per account.

The results in Excel.

Let’s do the same in Power BI, in case you prefer using Power BI.

Get data – CSV.

Transform data.

Change data type for account as text. No plans to use account as numeric data.

Add calculated column Date – month – month.

Close & Apply.

Then we create measure just like in Power Pivot.

Sum for debit side.

Sum for credit side.

Totals are counted credit minus debit as revenue increases in credit side.

The results are the same as in Excel. Power BI is presenting the results more graphically than Excel.

In this blog page I show how an Access database can be used as source of data for Excel Power Pivot or Power BI.

The Access database table consists of three fields: indexed ID field, product and sales volume.

The table is shown here is design view. ID is the field with primary key.

For the product-field a validation rule is set that only validated values for the fields are P1, P2 and P3.

In Excel select data – get data -from database – from Microsoft Access Database.

Access database is a ready made option as a source type for Excel.

Transform data.

Data type for ID field was changed to text as we have no plans to execute any calculations with ID field.

Take close & load – close & load to.

Create a measure to count total sales volume.

Create a pivot on top of data model.

Let’s create a graph based on data.

The access table was updated with few extra lines, the table was saved.

In Excel side, take power pivot – analyse – refresh.

Sales for P1 increased.

In Power BI select get data – access database.

Access database is, like in Excel, a standard data source type for Power BI.

Transform data. Just like in Excel.

ID data type was changed to text.

Select close & apply – close & apply.

Tot_sales = sum(Sales_t[Sales_vol])

Create a visualization.

Choose dimensions.

The results.

Now let’s change the Access data table.

The sales have been boosted for the product P2.

Fresh the data in Power BI.

The sales report by controller has been updated.

The point in this blog is that Access database is a very suitable source for both Power Pivot and Power BI. A benefit with Access is that data and report are separated. Data is not just a csv file, which was loaded from a system. If csv should be updated, then a new csv file should be taken from the source system.

Once the connection between the Access and reporting has been established, Access can be handled and updated via Access interface and at the same time reporting can be taken elsewhere.  

In this blog, I will check how easily geographic data can be handled in Power Bi compared to Excel.

Let’s have sales report with geographic data divided to four tables.

Tables are loaded to data model.

Tr_ord is the only transaction table with dimensions of order number, customer, product and sales quantity. Order number was changed to text as we don’t plan calculate anything with order number.

Customer master md_cust defines customer code, customer’s country and customer class large or small.

Product master data includes product code, name and sell price.

Continent based on customer’s country.

Data model is straightforward.

Relationships:

Creating the measures.

A simple measure to count order quantity.

DAX: = SUM(tr_ord[Quan])

A measure to multiply sales quantity of a material by corresponding material price.

DAX is here for copy pasting:

=sumx(tr_ord,[Quan]*RELATED(md_prod[Price]))

For the customer in Singapore two pieces of P3 and two pieces of P4 were sold. Price for P3 is 2,5 € and P4 is 2 €.

Pivot table selections.

This report has correct values but it could be more visual.

One option is to use Power BI.

Power query in Power BI looks similar to Power query in Excel.

Power BI created relationships automatically.

Relationships are same as in Excel even though interface is bit different.

The DAX is the same as in Excel, again interface is slightly different.

We add a visualization.

A multi-row card to check values.

Select the fields country from md_cust and measure sum in sales quan.

Select country from md_cust and measure sales in money in tr_ord.

The values are same as in Excel.

Select the map.

Select now sales quantity measure.

The map visualizes much better than a table. The bigger the ball is, the higher the sales are in that country.

Now select the filled map.

Filled map tells us where the markets are, meaning where are your customers. Unfortunately, Singapore is not visible.

You must see the difference if you compare this to Excel. Excel is not a bad tool at all, but Power BI is made to visualize data. Even a minor example clarifies the case. A good thing was, that you don’t have to customize the maps at all, just add visualization and select fields to be reported. Power BI reads directly the report with country names.