Monthly Archives: October 2020

Business Intelligence, Power BI

Geographic data with power pivot and power bi

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.

Business Intelligence, Graphics

Graphs with power pivot

This blog is to demonstrate how to make a somewhat interactive graphs with Power Pivot.

First, we have data in a sales report in CSV format. Report consists of 120 lines and five different dimensions. Descriptive dimensions are product, region and sales rep. The value dimensions are sales value and margin.

CSV-data is checked with notepad.

We need to upload the data to the data model, as usual.

Data type for rep was changed from number to text. Even though the rep is a number, but we won’t do any calculations with rep, so it is better to change the rep as text. Other than that, this looks good.

A short sales report is now in data model.

A pivot uses the workbook’s data model.

Measure created for total sales.

A measure for the margin.

Sales and margin per sales rep.

Absolute figures are good, but also relative values should be visible to see the margin percentage per sales representative.

A measure to calculate margin percentage simply by dividing margin by sales volume.

Total sales and margin %.

A graph is needed to visualize the absolute sales volume and margin percentage.

Let’s take a bar chart.

This does not look good. The margin percentage is compared to absolute sales volumes.

Click the mouse in the middle of the chart, that you get frames around the chart. Then right click the mouse and select change chart type.

Select combo at the lower left hand corner. Check that chart type for Margin % Mes is line and activate the secondary axis for the margin percentage.

Now the graph looks better. The scale for the sales is on the left, and another scale for margin is on the right.

It is obvious based on the graph, that rep number three is selling lowest volume but the margin percentage is double compared to other reps.

Sales rep in column was changed to region.

The Pivot got updated automatically.

The graph is updated. Region West is selling far more in value than other regions. The margin percentage is clearly the highest among the regions.

Another option is just to take Total sales per sales rep and count how many percent eg. Sales rep 1 is selling out of the total sales. In this case it would be 887/3878 which equals to roughly 23 %. But it is better to count that dynamically with a measure.

Let’s create a measure to have total sales 3878 as a parameter.

Calculate with CALCULATE, first argument is the Total sales measure, then use ALL, to count ALL in Sales report table.

Having Total sales mes and All sales mes in value field. All sales mes shows the same figure as grand total for the Total sales mes. All sales mes indiciates the value for each line.

Divide Total sales measure with newly created All sales measure to get the relative value.

The All sales mes -measure is not needed in the screen but it is a background value for the Sales % total mes.

Just remove the Total sales Mes as it is not needed in the graph. Sales rep 1 sold close to 23 % of total sales as we calculated manually. Now the value is dynamically calculated with a measure.

Select insert – charts – doughnut.

Would be nice to see the percentages.

Activate the graph, select add chart element – data labels – show.

Sales rep 1 sold the most and is the only one exceeding 20 % limit.

Change the rep to region as the row parameter.

Then we will see in the report how the total sales were divided between different regions.

The graph got updated automatically. West is selling nearly half of the total turnover.

The sales data we have is tiny. Still taking the sales data to data model, enables us to have graphs updated automatically once we take different dimensions to the power pivot.

Excel

Optimising the cost volume profit analysis with solver

A company is selling two products A and B. Product lines are independent of each other, the lines can make decisions independently. Sales price for A is 1,5 € and B 2 € per product. Variable costs for A are 0,75 € and B 1,05 € per product. The fixed costs for A are 200 € and for B 300 €. Variable costs are correlating with sales quantity as fixed costs are always unchanged.

There are some limitations for company. The company can sell max 500 pc of A and 500 pc of B. However, company can sell 850 pc of both A and B in total. The company has money for 1250 € for costs.

How many pieces of product A and B the company should sell in order to maximize the profit ?

This might sound like a tedious math exam. However, our best friend in a case like this is Excel.

First, we need to activate the Solver add in. Add in is an extra plug-in. It is not visible at first, but once we activate it appears in a ribbon.

Take file-options.

Select Add-ins at left hand side, then browse solver add-in in the middle. Press go, do not press yet ok.

Check the solver add-in and press ok.

Now get back to Excel and take data-ribbon.

Check that utmost icon on the right is solver.

Lay down the cost volume profit analysis.

Sales in money equals to sales volume multiplied by sales price. Sales volume can be changed but sales price is given value.

Variable costs are simply the single cost multiplied by sales volume.

Margin is sales minus variable costs.

Fixed costs are static 200 for A and 300 for B.

Result per product is margin minus fixed costs. Result in total is result for both A and B.

Constraints are also keyed in. The constraints are collected in own area and they take references from cost volume profit analysis.

Max sales quantity for A is 500.

Max sales quantity for B is 500.

Max sales quantity for A and B in total is 850.

Max cost level is 1250.

Numbers in Excel are different, but we have not started the solver yet.

Now it is time call up the solver add-in.

Set objective the cell to be maximized, the total profit.

Changing variable cells are the sale volume cells.

Non-negative variable means that we do not sell or produce negative quantities.

We use linear programming method.

Press solve.

Press ok.

For a surprise at least for me, solver does not propose to sell any of the products for full 500 pc.

The solver proposes that most of the total profit would be generated by product A. Margin percentage is bit higher for A than B. Also the fixed costs are lower for A than B. Sales in money is higher for B as the sales price is also higher. Margins are even.

Should not you product the whole 500 pc of A ? That is anyway generating more profit.

We can manually change the sales volumes. If sales volume for A is 500 and B 350, the total profit is 207,5. Increasing the sales volume for A caused profit to increase by 18,75. However, the profit for B dropped by 23,75. Looks like the solver got the case solved.