SYNOPTIC DESIGNER FOR POWER BI – CUSTOM VISUALS

Mit dem Synoptic Designer für Power BI können individuelle Grafiken erstellt und integriert werden. Hierfür nutzen ich den Power BI Designer (Desktop Version)  für Windows.
[Download: https://powerbi.microsoft.com/de-de/desktop/]

Aufbau

In diesem Artikel möchte ich demonstrieren wie ein Fußballfeld in einen Bericht integriert wird, um Statistiken aus einem Fußball-Spiel visuell besser darstellen zu können.

Das Spielfeld ist schnell selbst gezeichnet oder aus dem Internet geladen und auf der Synoptic Webseite [https://synoptic.design/ ] wird dies eingefügt und die Bereiche werden markiert:1

Daten

Die korrespondierende Excel Tabelle mit den Daten ist folgendermaßen aufgebaut:

2

Die Spalte „Area“ gibt auch den Namen im Designer, dadurch erfolgt die Zuordnung der Daten auf das Spielfeld, bzw. die Grafik.

Integration

Im Designer exportiert man nun die Grafik mit den Markierungen via „Export to Power BI“ als SVG Datei.

Zuerst wird aber die Excel Tabelle in den Power BI Designer importiert:

3

Anschließend erstellen wir noch folgende Measures zum zählen des Spielstandes:

  1. Tore Team B = CALCULATE(COUNTA(Spieldaten[Aktion]); Spieldaten[Aktion]=“Tor“; Spieldaten[Team] =“B“)
  2. Tore Team A = CALCULATE(COUNTA(Spieldaten[Aktion]); Spieldaten[Aktion]=“Tor“; Spieldaten[Team] =“A“)
  3. Tore Gesamt = [Tore Team A]+[Tore Team B]

 

Der nächste Schritt ist, die sogenannten Custom Visuals in den Designer zu verankern, dafür wählt man auf der Webseite „Get the latest Version“ und lädt die Datei herunter. Anschließend wird diese integriert:

4

Nun kann dieses Panel in den Bericht integriert werden.

Design und Aufbereitung

Nachdem das Panel positioniert wurde, werden die Felder aus den Daten eingesetzt:

Area -> Legend

Aktion -> Values

5

 

 

 

 

 

 

 

 

Anschließend wird via „select map“ die heruntergeladene Karte (SVG Datei) ausgewählt.

Nun werden noch weitere Grafiken positioniert und befüllt:

6

 

 

 

 

 

Okay, anschließend noch das Dashboard auf das kostenlose Power BI Konto hochladen und im Web veröffentlichen:
Link

Excel 2013 and the Flash Fill Function (ETL for Beginners ;-) )

Off topic but really good to combine with PowerPivot or PowerQuery: The flash fill function.
With this feature in Excel 2013 you can automatically extract text into columns. It works better than the text in columns feature or Left, Right or Find functions in Excel or PowerPivot for me.

Example:

This column contains surname, middle names and last names into one column. First you must convert this data into a table:

10-04-_2015_15-01-42

Next add a column “surname” – please do not add the other columns. Sometimes Excel is confused by the other columns…
Then type in the new column the first surname “Franz”. In the next row start typing the next surname “Karl”. Excel writes automatically the other names into the cells. In this case you can also use the Flash Fill Function in the Ribbon “Data” – but this function will not work by the middle names.

10-04-_2015_15-04-55

 

 

 

 

 

 

 

Now add a new column middle name and type in the first data cell the first middle name “Dieter”. If your data have no double name in the first rows, sometimes you must type much more names if Excel use the Flash Fill Function.

10-04-_2015_15-05-40

 

 

 

 

 

 

 

 

In the last row you can see the surname in the middle name column. If you have middle names or data with more text parts it is sometimes not perfect! The last step is to add the last name column. The data has in the first 2 rows some different data – a single last name and double name. So you must type 2 rows till the Flash Fill Function takes effect:

10-04-_2015_15-06-08

Now you can add this table into your PowerPivot or PowerView model. Enjoy this feature!

More information: http://blogs.office.com/2012/08/09/flash-fill/

Wasserfall Diagramme mit PowerPivot

PowerPivot_Logo(english version below)
Nachdem ich bisher Wasserfall Diagramme manuell in Excel erstellt habe, möchte ich hier zeigen, wie das auch in Verbindung mit PowerPivot für Excel funktioniert. Die Datengrundlage ist die gute alte Northwind Datenbank. Um ein Wasserfall Diagramm zu erstellen, benötige ich folgende Informationen:

  • Aktueller Umsatz
  • Umsatz des letzten Monats
    • Verringerung und
    • Vergrößerung des Umsatz
  • Zeit Informationen

Mit folgendem Query lese ich die Daten und löse das Pivot auf, damit ich die Daten besser in PowerPivot verarbeiten kann:

SELECT
 YearNumber
 , MonthNumber 
 , Amount
 , AmountType
 , CASE AmountType
  WHEN 'BaseValue' THEN 1
  WHEN 'Increase' THEN 2
  WHEN 'Reduction' THEN 3
 END As CategorySort
FROM ( SELECT 
  CASE 
   WHEN Data.SalesAmount - Data.SalesAmountLastMonth  < 0 
    THEN (Data.SalesAmount - Data.SalesAmountLastMonth) * -1 
    ELSE 0
   END As Reduction
  , Data.SalesAmountLastMonth - 
   CASE 
   WHEN Data.SalesAmount - Data.SalesAmountLastMonth  < 0 
    THEN (Data.SalesAmount - Data.SalesAmountLastMonth) * -1 
    ELSE 0
   END As BaseValue
  , CASE 
   WHEN Data.SalesAmount - Data.SalesAmountLastMonth  > 0 
    THEN (Data.SalesAmount - Data.SalesAmountLastMonth)  
    ELSE 0
   END As Increase
  , Data.MonthNumber
  , Data.YearNumber
 FROM (
  SELECT
   MONTH(Orders.OrderDate) As MonthNumber
   , YEAR(Orders.OrderDate) As YearNumber
   , SUM(Quantity*UnitPrice) As SalesAmount
   , (
    SELECT SUM(od1.Quantity*od1.UnitPrice) FROM [dbo].[Orders] o1
    INNER JOIN [dbo].[Order Details] od1 ON od1.OrderID = o1.OrderID
    WHERE MONTH(o1.OrderDate) = MONTH(DATEADD(mm, -1, MAX(Orders.OrderDate))) 
    AND YEAR(o1.OrderDate) = YEAR(DATEADD(mm, -1, MAX(Orders.OrderDate)))
   ) As SalesAmountLastMonth
    FROM [dbo].[Orders]
    INNER JOIN [dbo].[Order Details] ON [Order Details].OrderID = Orders.OrderID
    WHERE Year(Orders.OrderDate) = 1997
    GROUP BY 
   MONTH(Orders.OrderDate) 
   , YEAR(Orders.OrderDate)
  ) data
 ) t
 UNPIVOT (Amount for AmountType in (Reduction, BaseValue, Increase)) As Amount
ORDER BY YearNumber, MonthNumber

 

Nachdem diese nun eingelesen wurden sind, habe ich zu jedem Monat 3 Zeilen mit den notwendigen Informationen. Nun lasse ich die jeweilige Kategorie noch sortieren, damit der Basis Wert im Diagramm unten liegt. Übergibt man nun die Daten in ein gestapeltes Diagramm, sieht das folgendermaßen aus:

1

 

2

 

 

3

Nun müssen lediglich folgende Anpassungen gemacht werden:

  • Die Kategorie mit dem Basis Wert ohne Farbe einstellen
  • Verlustwerte rot färben
  • Gewinnwerte grün färben
  • Die Beschriftungen ausblenden

4

Voila! Fertig ist das Wasserfall Diagramm mit Hilfe von PowerPivot.

 

Waterfall Chart with PowerPivot

 

After I’ve created waterfall charts manually in Excel, I want to show here how this works in conjunction with PowerPivot for Excel. The data is from the good old Northwind database.

To create a waterfall chart, I need the following information:

  • Current sales
  • Turnover last month
  • reduction and increase in the sales
  • Time information

With the following query I read the data and make a unpivot so I can better handle the data in PowerPivot:

SELECT
 YearNumber
 , MonthNumber 
 , Amount
 , AmountType
 , CASE AmountType
  WHEN 'BaseValue' THEN 1
  WHEN 'Increase' THEN 2
  WHEN 'Reduction' THEN 3
 END As CategorySort
FROM ( SELECT 
  CASE 
   WHEN Data.SalesAmount - Data.SalesAmountLastMonth  < 0 
    THEN (Data.SalesAmount - Data.SalesAmountLastMonth) * -1 
    ELSE 0
   END As Reduction
  , Data.SalesAmountLastMonth - 
   CASE 
   WHEN Data.SalesAmount - Data.SalesAmountLastMonth  < 0 
    THEN (Data.SalesAmount - Data.SalesAmountLastMonth) * -1 
    ELSE 0
   END As BaseValue
  , CASE 
   WHEN Data.SalesAmount - Data.SalesAmountLastMonth  > 0 
    THEN (Data.SalesAmount - Data.SalesAmountLastMonth)  
    ELSE 0
   END As Increase
  , Data.MonthNumber
  , Data.YearNumber
 FROM (
  SELECT
   MONTH(Orders.OrderDate) As MonthNumber
   , YEAR(Orders.OrderDate) As YearNumber
   , SUM(Quantity*UnitPrice) As SalesAmount
   , (
    SELECT SUM(od1.Quantity*od1.UnitPrice) FROM [dbo].[Orders] o1
    INNER JOIN [dbo].[Order Details] od1 ON od1.OrderID = o1.OrderID
    WHERE MONTH(o1.OrderDate) = MONTH(DATEADD(mm, -1, MAX(Orders.OrderDate))) 
    AND YEAR(o1.OrderDate) = YEAR(DATEADD(mm, -1, MAX(Orders.OrderDate)))
   ) As SalesAmountLastMonth
    FROM [dbo].[Orders]
    INNER JOIN [dbo].[Order Details] ON [Order Details].OrderID = Orders.OrderID
    WHERE Year(Orders.OrderDate) = 1997
    GROUP BY 
   MONTH(Orders.OrderDate) 
   , YEAR(Orders.OrderDate)
  ) data
 ) t
 UNPIVOT (Amount for AmountType in (Reduction, BaseValue, Increase)) As Amount
ORDER BY YearNumber, MonthNumber

After I imported the data, I have 3 lines for each month with the necessary information. Now I let the respective category yet sort, so that the base value is in the chart below. Then we can create a stacked bar chart:
1

 

2

 

 

3

 

Now only the following adjustments must be made​​:
– Set the category to the base value without color
– Reduction is set to red
– Increase is set to ​​green
– Hide the Labels

4

Voila! Finish is the waterfall chart using PowerPivot.

 

PowerPivot: Switch() Funktion – oder auch: Wie komme ich ohne Hilfstabellen bei Übersetzungen aus?

PowerPivot_LogoEnglish version below

Wenn man einfache Übersetzungen in Daten vornehmen wollte, bspw. Gruppenschlüssel in Namen zu übersetzen, bediente ich mich bisher immer an Hilfstabellen die ich in Excel definiert und dann in das Datenmodell eingebunden haben.

So in etwa – eine Tabelle beinhaltet einen Schlüssel um eine Kundengruppe zu beschreiben. Die Beschreibung befindet sich in einer Hilfstabelle die in PowerPivot eingebunden wird und per Verknüpfung dann die Übersetzung vornimmt. Problem dabei ist meist, wenn ein Wert in den Daten vorkommt, der nicht in der Übersetzung Tabelle vorkommt. Da habe ich dann per einer berechneten Spalte per Bedingter Anweisung die fehlenden Werte markiert.

Hier der vorherige Weg:

2014-08-18_12-37-08 2014-08-18_12-39-03

 

 

  

  

 

  

 

 

 

 

 

Hier kommt nun die SWITCH Funktion ins Spiel.

Mit dieser kann ich Werte überprüfen und übersetzen. Grundsätzlich geht das Ganze auch mit einer IF Funktion, aber deutlich unübersichtlicher und somit schlecht in der Wartung.

=SWITCH(Customers[Group]; "A"; "High Priority"; "B"; "B Customers"; 
"C"; "C Customers"; "no valid group")

Der Grundsyntax von SWITCH:

SWITCH(expression, 
    value1, result1,
    value2, result2,
     :
     :
     else
    )

2014-08-18_12-41-07

 

 

PowerPivot Switch Function – or the end of the linked translation tables?

If you wanted to make simple translations in data, for example I want to translate a Customer Group Key in a name, I used to create translations Tables in Excel and then integrated into the data model of PowerPivot.
Something like that – a table contains a key to a customer group to describe. The description is another table that is linked in PowerPivot and then performs the translation by a relationship. Problem is mostly, if a value exists in the data that is not present in the translation table. Since then I have checked the missing values ​​by a calculated column using a IF/ELSE statement.
Here the previous path:
2014-08-18_12-37-08 2014-08-18_12-39-03

 

 

  

  

 

  

 

 

 

 

 

Here are the SWITCH function:
With this I can check values ​​and translate. Basically, the whole thing goes well with an IF function, but much less clear and thus poorly in maintenance.

 =SWITCH (Customers[Group], "A", "High Priority", "B", "B Customers", 
"C", "C Customers", "no valid group")

The basic syntax of SWITCH:

SWITCH(expression, 
    value1, result1,
    value2, result2,
     :
     :
     else
    )

2014-08-18_12-41-07
 

Standardfeldsatz in PowerPivot und PowerView

PowerPivot_LogoTabellen können im PowerPivot Datenmodell so vorbereitet werden, dass man in einem Klick eine vorgefertigte Aufbereitung in PowerView erhalten kann.

see english version below

Als Datengrundlage dient hier eine Abfrage aus der Adventure Works:

SELECT Production.Product.ProductID, Production.Product.Name ProductName, Production.Product.Color, Production.Product.ListPrice, Production.ProductCategory.Name AS Category, Production.ProductPhoto.ThumbNailPhoto, 
                  Production.ProductSubcategory.Name AS Subcategory
FROM     Production.Product INNER JOIN
                  Production.ProductProductPhoto ON Production.Product.ProductID = Production.ProductProductPhoto.ProductID INNER JOIN
                  Production.ProductPhoto ON Production.ProductProductPhoto.ProductPhotoID = Production.ProductPhoto.ProductPhotoID INNER JOIN
                  Production.ProductSubcategory ON Production.Product.ProductSubcategoryID = Production.ProductSubcategory.ProductSubcategoryID INNER JOIN
                  Production.ProductCategory ON Production.ProductSubcategory.ProductCategoryID = Production.ProductCategory.ProductCategoryID

Als erstes markiert man in PowerPivot das Feld „ThumbNailPhoto“ als Bild:
piv

Im nächsten Schritt werden die Felder konfiguriert, die bei einer Analyse als Standard in der Reihenfolge ausgegeben werden sollen:
feldsatz

Nun muss das Tabellenverhalten noch so eingestellt werden, dass bei der Verwendung das Bild automatisch mit verwendet wird:
tabellenverhalten

Nun kann in PowerView durch einen Doppelklick auf die Tabelle die Liste erstellt werden. Stellt man nun die Ansicht von der Tabelle auf Karten um, ist die Analyse durch 2 Klicks für den Anwender aufbereitet.
doubleklick

karte

Weitere Informationen gibt es hier:

English Version

Tables can be prepared in the PowerPivot data model so that you can get a ready-made presentation in Power View in a single click.
Following Query are used for this example (Adventure Works)

SELECT Production.Product.ProductID, Production.Product.Name ProductName, Production.Product.Color, Production.Product.ListPrice, Production.ProductCategory.Name AS Category, Production.ProductPhoto.ThumbNailPhoto, 
                  Production.ProductSubcategory.Name AS Subcategory
FROM     Production.Product INNER JOIN
                  Production.ProductProductPhoto ON Production.Product.ProductID = Production.ProductProductPhoto.ProductID INNER JOIN
                  Production.ProductPhoto ON Production.ProductProductPhoto.ProductPhotoID = Production.ProductPhoto.ProductPhotoID INNER JOIN
                  Production.ProductSubcategory ON Production.Product.ProductSubcategoryID = Production.ProductSubcategory.ProductSubcategoryID INNER JOIN
                  Production.ProductCategory ON Production.ProductSubcategory.ProductCategoryID = Production.ProductCategory.ProductCategoryID

The first thing you marked in PowerPivot the „Thumbnail Photo“ field as an image:
piv

In the next step, the fields are configured to be output in an analysis as the standard, in order:
feldsatz

Now, the behavior of table must be set so that in use, the image is automatically used with:
tabellenverhalten

Now you can create in Power View, double-click the table list. If, now, the view from the table to map to, the analysis is prepared by 2 clicks for the user.
doubleklick

karte