Power BI is generally available now

Ivan Bondarenko July 26, 2015 No Comments

From the first glance Power BI is a dream as self-service BI if only cloud publishing platform is allowed in organization.

Set of short videos shows how easy is to work with Power BI
Power BI Desktop GA Update Overview – overview of new features of Power BI Desktop application.

How to create and use the Personal Gateway to keep your data up to date. – connect reports in cloud with on-prem data through own workstation.

How to create content packs for your organization to use in the Power BI service. – packs of reports, create, share with groups of colleagues.

How to connect a file from OneDrive to Power BI. – very useful scenario, when workbook with data stored on Onedrive.

I consider important to try “packs of reports” feature. It is easy when reports are done for one small company, where reports divided on two or three groups; but will it be easy to maintain such structure on Power BI for 10 countries with 5 areas of reports in each, where every group should be isolated?

ABC-analysis, method of Triangle in Excel

Ivan Bondarenko February 25, 2015 No Comments

This post continues topic described in two previous posts – ABC analysis. Very popular, simple method, however in most of cases non-accurate.
In previous post I showed how to increase accuracy of ABC-analysis by using special method – Triangle method. And now time to show real example.

Assume that we have list of sales

3-1

To apply method of triangle we have to
1. Sort data descending by Net Value

3-2

2. Convert Net Value to Net Sales – exclude possible Returns.
=IF([@[Net Value]]<0, 0, [@[Net Value]])

3-3

3. For each row calculate part of total Net Value – NS Normalization: [@[Net Sales]] / SUM([Net Sales])

3-4

4. For each row calculate [Contribution in result]: sum of [part of Net Value] from 1st ranked position till current row =SUM(SALES[[#Headers],[NS Normalization]]:[@[NS Normalization]])

3-5

5. Make normalization of rank: =[@Position] / MAX([Position])

3-6
6. Find Pareto Point – point where [@[Contribution in result]] + [@[Position Normalization]] = 100% (or closest point).

3-7

Resulting table

3-8

Then we will be able to find Xp and Yp – coordinates of Pareto Point.
In example file I placed these coordinates in separate cells and gave corresponding names to that ranges: “Xp” and “Yp”.

3-9

After that, using formulas

3-10
Which in Excel are:
=(Xp*Yp – Xp^2) / (2*Yp)
=(3*Xp*Yp + Xp^2) / (2*Yp)

We can find upper bound of groups A and B. And then mark all positions with corresponding class.

3-11

Such calculation can be easily done in PowerPivot where we can avoid sorting and grouping of transactional sales data – DAX can handle all necessary calculations.

Example workbook can be found here.

Method of triangle in ABC-analysis

Ivan Bondarenko January 21, 2015 No Comments

Hi there! In this post you will find overview of triangle method in ABC-analysis, its pros and cons, and advantage over classical methods.

Firstly, remember basics of Classical ABC-analysis.
The simplest method of classification can be called method of segmentation on three equal groups by quantity of positions or by value of result.

Simplest method

However, this method is only useful for even diminishing series (arithmetical progression). In practice, such distributions are rather exceptions. In examples below, you will see how irrational can be segmentation on equal groups.

Simplest method 2
(more…)

Methods of classification in ABC analysis

Ivan Bondarenko January 15, 2015 No Comments

Hi all, I would like to start here with article about methods of classification in ABC-analysis. I tried to find such compilation in Google, but couldn’t, even couldn’t find info about some methods described below.

Introduction

ABC-analysis – one of simple objects classification methods using defined parameter. Although methods that use one parameter limited, ABC-analysis is one of the most popular analytical tool nowadays. Due to this popularity, plenty of algorithms for grouping items in ABC-analysis were developed. This, from the one hand, gives analyst rich choice of methods for different goals and technical resources, but, from the other hand, contains hidden threats in case of wrong usage of varying methods.

Process of ABC-analysis can be divide on five following steps:

  • Definition of goals
  • Selection of classification parameter
  • Data collection and preparation
  • ABC-classification
  • Interpretation of results
  1. On first step should be defined analysis goals. They influence on further steps.
  2. On second step, depending on goals, parameter of objects classification should be chosen.
  3. Third step contains process of collection and preparation of raw data for analysis.
  4. Using one of algorithms and chosen parameter on fourth step should be processed ABC-classification.
  5. On the last step, results can be observed and needed decisions taken.

In this article considered seven most used methods of grouping in ABC-analysis, shown their pros and cons, and in addition comparison of these methods.

(more…)