10 Jun 2014
Power Pivot is a great way to do data modelling and analysis right in Excel. It works great for data that is dimensionally organized (facts and dimensions) as well as other forms of data. It even enables you to define relationships between datasets regardless of source. However, one thing that has been hard is the following: consider the scenario where you have stock values for certain stocks for certain days, like below:
Let’s also assume you have a table that shows marketshare (or something else) per company on a certain date, like this:
Finally, you have a third table that lists the stock label by company, like so:
Now, assume that you would like to add the stock value of a company on a certain date next to the market share for that company at that date so as to provide more context to a potential relationship between market share and stock value. Maybe a bigger market share has an impact on stock value?
Naturally, what you would do is load these tables into Power Pivot so you get the following:
Now, the next step would be to add relationships between these two tables. The relationships should be defined as follows:
I.E.: Stock labels and Market shares are related on the Company column, whereas Stock and Stock Labels are related on the Stock / Stock label column.
We can now try to get the stock value for the company at a certain date, but how? Just using RELATED() to get stock values will not work as it will return a table. You could use MAX or MIN to then get a maximum or minimum value, but that is not what we are after: we wanted to return the stock value for that company at exact that date. More generally, this problem occurs when a table is related “twice” to another table, such as monthly targets by person vs. actuals (the relationship between the actual and target table is double: both on month as well as person).
The solution is using LOOKUPVALUE() and here is how. In the Marketshares table I add a calculated column with the following definition:
=LOOKUPVALUE(Stocks[Value];Stocks[Stock];RELATED('Stock Labels'[Stock Label]);Stocks[Date];Marketshares[Date])
This might seem complex, so allow me to explain. What this does is the following:
Look up and return a value from
The Value column in the Stocks table (Stocks[Value])
For which
The stock label is equal to the stock label on record for the company (RELATED(‘Stock Labels’[Stock Label]))
And
The date equals the date of the market share information.
The result is:
Pretty nifty huh? Turns out that to use LOOKUPVALUE() this way you do not even have to be able to relate the lookup table to the data model at all. In my example the relationship between Stocks and Stock Labels is not even necessary, although I find it good practice to include all relationships just for clarity.
03 Jun 2014
If you ever used Power Pivot to calculate things such as sales amount per capita or averages of some sort you will have run into the situation that the denominator (the column you want to divide by) is empty or zero. To cope with a potential division by zero, Power Pivot outputs Infinity. This can be seen in the screenshot below where the ‘Per Capita’ column is defined as:
=[SalesAmount]/[Numer of citizens]
Of course you can fix this by using
IF(ISBLANK([InputColumn]))
and others to work around the error. However, the DIVIDE() function makes this all a lot easier!
The DIVIDE() function takes two required and one optional parameters, which are: numerator, denominator and an optional value to return when division by zero occurs.
To see what DIVIDE() does, consider the following screenshot:
The Divide1 column here is defined as:
=DIVIDE([SalesAmount];[Number of citizens])
Whereas the Divide2 column contains the following function:
=DIVIDE([SalesAmount];[Number of citizens];0)
The results are great, no Infinities are returned! By default DIVIDE() returns empty in case of a problem (Divide1 column). You can override this by specifying the third parameter so to return a fixed value in cased of a problem (Divide2 column in my example).
Hope this helps!
27 May 2014
For those of you that would like to get up to speed with Power BI but rather read a book, here are some suggestions:
- For Dutch readers: Praktijkboek PowerPivot in Excel
- DAX Formulas for PowerPivot by Rob Collie
- Microsoft Excel 2013 Building Data Models with PowerPivot by Marco Russo and Alberto Ferrari
13 May 2014
Below is a list of learning resources for Power BI that I am aware of. I strongly believe in less reading, more doing with regards to Power BI, but to get a feeling of where to start and what the solution can and cannot do these might help:
Once you have looked at some (or all) of these just start using the tools and applying them to your situation! That’s the best way to learn. If I missed any resources, please let me know.
06 May 2014
Normally I blog in English, but since this post is specifically about support Dutch postal codes in Power Map, this post will be in Dutch. For those interested, the answer is: Dutch postal codes are supported in Power Map, but you need to use the first four digits and strip off the last two characters. Better stil: use the address (if you have it).
Een veel gestelde vraag van mijn klanten is: is er ondersteuning in Power BI / Power Map (Geoflow) voor Nederlandse geografische informatie, zoals postcodes, steden en adressen? In deze post wil ik antwoord geven op deze vraag met de volgende dataset:
Met deze data kun je gemakkelijk een Power Map maken door in Excel op Insert / Invoegen te klikken en dan voor Map te kiezen. Als Power Map gestart is worden Address en City al automatisch op de kaart geplaatst. Klik op ‘Postal Code’ en kies ‘Zip’ om deze ook aan te zetten (zie onder):
Nu, als je Address aanklikt dan wordt de data perfect geplot op de gekozen locaties:
Dus: Nederlandse adressen werken prima.
Als we vervolgens overstappen naar City, dan ontstaat het volgende:
Opnieuw, klopt perfect.
Echter, als we nu ‘Postal Code’ kiezen dan krijgen we een lege kaart. Dit komt omdat Power Map op dit moment alleen de eerste vier getallen van een Nederlandse postcode kan ondersteunen en niet de volledige vier getallen plus twee karakters. Dus 1000 AA werkt niet, maar 1000 wel. Natuurlijk is dit minder specifiek dan 1000 AA. Als je dus de laatste twee karakters van de postcode afhaalt werkt de mapping weer:
Concluderend: Nederlandse geografische informatie wordt ondersteund en als je adressen hebt kun je zelfs heel specifiek plotten. Als je alleen postcodes hebt kun je op dit moment niet de volledige postcode gebruiken, maar alleen de eerste vier getallen. Overigens kun je met die vier getallen wel regions laten plotten op je kaart :)
Dutch Data Dude