Dynamic colored R Diagram in Power BI using Earthtone

Power BI integrates R to perform complex analysis and sophisticated visualization. Earthtones is an R library which takes a screenshot from Google Maps of certain geo coordinate and extracts the landscape colors. Earthtones can be used to color diagrams based on the local color schema.

Earthtones

The package can be found on github. There is also a description how to donwload and install the package. Using earthtones is easy. The function get_earththones takes the parameters longitude and latitude, zoom and the number of colors to extract. The earthtones for Steyr look like this:

get_earthtones(latitude=48.045,longitude=14.422,zoom=15,number_of_colors=8)

Steyr Earthtones

Power BI Data Model

The data model in this example is very simple. There are two excel sheets, one for the revenue by city and item group, another for the geo coordinates (longitude / latitude) and optimal zoom level per city.

Excel Sheet Revenue per City and Item Group

City Geo Coordinates

The Power BI model is very simple, both data sources are linked by the city name

Power BI Data Model

R Boxplot diagram in Power BI

In this example a simple boxplot is used to visualize the revenue by item group. A data slicer for the column city is used to filter the data. The R diagram takes the following columns as input:

  • Longitude
  • Latitude
  • Zoom
  • City
  • Price
  • Group

If only one city is selected, the R script shall gather the cities earthtone colors and format the diagram. If more than one city is selected, the diagram shall be formatted in red, blue and green. The following script loads the earthtone library and gets the distinct number of city names from the dataset. If there is more than 1 distinct name in the dataset the color variable is set to red,blue,green. Otherwise, earthtone is used to get the city typical color schema.

library(earthtones)

numCities <- length(unique(dataset$Stadt))
if(numCities > 1) {
color <- c(“red”,”blue”,”green”)
} else {
color <- get_earthtones(latitude = dataset$Lat[1],
longitude=dataset$Lon[1],
zoom= dataset$Zoom[1],
number_of_colors=3,include.map=FALSE)
}

boxplot(Preis~Gruppe,dataset,col=(color),ylab=”Revenue”,xlab = “Item Group”)

The R script in Power BI looks like this:

R Script and Boxplot in Power BI

If a city is selected, for example San Francisco, the diagram is formatted in the colors blue, gray and brown.

R Diagram in Power BI with dynamic color

The colors fit the blue sea, the bay and the city seen from space.

R Earthtone for San Francisco

If another city, for example Cairo, is selected the diagram gets formatted in dark green, dark- and light brown.

R Diagram in Power BI with dynamic color

That fits the cities local color schema, the brown buildings, the green plants along the Nile and the desert sand.

R Earthtone for Cairo

Create a Power BI Dashboard for Dynamics AX 2012 Sales

This is an update to the previous published articles on Data Visualization, OData Feeds, Power Map, Power Pivot in Office 2013 and Power Pivot in Office 2010. It shows how to use Power BI for Desktop to create a Sales Dashboard for Dynamics AX 2012 (R2).

Power BI Dashboard

Get Data

Start Power BI for Desktop and start with an empty report. From the ribbon on top click “Get Data”, choose SQ Server and provide your server and database. In this example I’m using a single server installation. However, in a production environment you might need to provide <SERVERNAME> \ <INSTANCENAME> , <PORT> e.g. SRVSQL\PROD,2303.

Get data into Power BI

In the next step you have to provide credentials. In my case I’m allowed to access the server with my domain account. In a production environment it is recommended to create a separate Login which is only used for BI Purpose. Don’t get confused if you get a warning that your SQL does not support encryption. If the connection was established successfully, the data wizard presents you a list of tables. Select the following tables:

  • CustInvoiceJour
  • CustInvoiceTrans
  • CustTable
  • InventTable
  • LogisticsPostalAddress

Select tables for Power BI

Click Load, and choose “Import” to load the data in Power BI for Desktop.

Transform Data

In Power BI for Desktop, at the Ribbon click “Edit Queries”. This will open the query editor. We don’t need all columns for this Demo. For each table click the “Choose Columns” button and the select only the following columns:

Choose columns for Power BI

CustInvoiceJour CustInvoiceTrans CustTable
  • InvoiceAccount
  • InvoiceDate
  • InvoiceId
  • NumberSequenceGroup
  • SalesId
  • InvoicePostalAddress
  • DataAreaId
  • InvoiceId
  • InvoiceDate
  • NumberSequenceGroup
  • ItemId
  • LineAmountMST
  • SumLineDiscMST
  • DataAreaId
  • AccountNum
  • CustGroup
  • DataAreaId
InventTable LogisticsPostalAddress
  • ItemId
  • ItemType
  • DataAreaId
  • Address
  • CountryRegionId
  • ZipCode
  • City
  • RecId

Click “Close & Apply” to finish this task.

Choose columns for Power BI

 

Create Relations

In Power BI for Desktop switch to the Data View (with the table symbol on the left pane). It is required to create primary keys and foreign keys before linking the tables. From the list of tables (on the right) select the CustTable and at the Ribbon click “New Column”. Type the following definition:

PK_Cust = CUSTTABLE[ACCOUNTNUM] & “-” & CUSTTABLE[DATAAREAID]

Create Foreign Keys in Power BI

This will create a new column with a customer account which is unique for all company accounts. Repeat this step for the following tables and columns:

InventTable:

PK_Item = INVENTTABLE[ITEMID] & “-” & INVENTTABLE[DATAAREAID]

CustTable:

PK_Cust = CUSTTABLE[ACCOUNTNUM] & “-” & CUSTTABLE[DATAAREAID]

CustInvoiceJour:

FK_Cust = CUSTINVOICEJOUR[INVOICEACCOUNT] & “-” & CUSTINVOICEJOUR[DATAAREAID]

PK_Invoice = CUSTINVOICEJOUR[INVOICEID] & “-” & CUSTINVOICEJOUR[SALESID] & “-” & CUSTINVOICEJOUR[NUMBERSEQUENCEGROUP] & “-” & CUSTINVOICEJOUR[DATAAREAID]

CustInvoiceTrans:

FK_Item = CUSTINVOICETRANS[ITEMID] & “-” & CUSTINVOICETRANS[DATAAREAID]

FK_Invoice = CUSTINVOICETRANS[INVOICEID] & “-” & CUSTINVOICETRANS[SALESID] & “-” & CUSTINVOICETRANS[NUMBERSEQUENCEGROUP] & “-” & CUSTINVOICETRANS[DATAAREAID]

Save, and open the relations by clicking on the relations item in the navigation pane on the left. You can drag&drop columns from one table to another table to create relations. Link the following columns:

  • CustInvoiceTrans,FK_Item > InventTable.PK_Item
  • CustInvoiceTrans.FK_Invoice > CustInvoiceJour.PK_Invoice
  • CustInvoiceJour.FK_Cust > CustTable.PK_Cust
  • CustInvoiceJour.InvoicePostalAddress > LogisticsPostalAddress.RecId

Your data model should look like this:

Create relations in Power BI data model

Name the ItemType

In the data view, select the InventTable. From the ribbon create a new column and name it “TypeName”. Add the following code to translate the Enum based ItemType Integer Value to a meaningful name.

TypeName = IF(INVENTTABLE[ITEMTYPE] = 0; “Item”; IF(INVENTTABLE[ITEMTYPE] = 2; “Service”; “Not an Item”))

The InventTable should look like this:

Name item type column

Create a Discount measure

Next we will create a measure which calculates the given discounts as the percentage of the total price. Open the data view using the second button on the left navigation pane. Select the CustInvoiceTrans. From the ribbon, click “New Measure” button in the “Modelling” tab. Provide the following code:

M_DiscPerc = sum(CUSTINVOICETRANS[SUMLINEDISCMST]) * 100 / ( sum(CUSTINVOICETRANS[LINEAMOUNTMST]) + sum(CUSTINVOICETRANS[SUMLINEDISCMST]))

For example:

Qty = 1 € , Unit Price = 1000 €     –> Price = 1000 €
Discount = 100 €                    –> Price = 900
Discount Percentage 3 %             –> Price = 873 €

CustInvoiceTrans.LineAmountMST = 873
CustInvoiceTrans.Discount = 100
CustInvoiceTrans.LinePercent = 3.0
CustInvoiceTrans.SumLineDiscMST = 127

M_DiscPerc = 127 * 100 / (873 + 127) = 12,7

Visualize

Switch to the empty report view using the first button on the left navigation pane. From the Visualization toolbox click the “Card”. This will place an empty card on the report. Drag&Drop the LineAmountMst from the CustInvoiceTrans on the empty card. It should look like this:

Power BI card chart

Next, place a map from the toolbox on the report. Drag&Drop the fields CountryRegionId, City and ZipCode from the LogisticsPostalAddress table on the Location. Drag&Drop the LineAmountMST from the CustInvoiceTrans on the Values Field. The map should look like this:

Power BI map chart

Add a new Gauge to the report and use the Measure M_DiscPerc as value. You cannot set a hardcoded Min. and Max. value in the data properties. Switch to the Format view using the pencil icon.  In the group Gauge Axis, set the Min. Value 0, the target value to 3 and Max. Value 100. Depending on your data, the gauge may look like this:

Power BI gauge control

Next a donut chart to visualize the revenue per item type. Drag&drop the LineAmountMST from the CustInvoiceJour on the value property of the donut chart and drag the TypeName from the InventTable.

Power BI donut chart

Place a column chart on the report to visualize the revenue per customer group. Place the LineAmountMST in the Value field. Use the CustGroup from the CustTable as Axis. Change the sort order to LineAmountMST by using the […] Dropdown Menu in the upper right corner of the chart.

Power BI column chart

Finally, add a line chart on the report to visualize the revenue per year. Place the LineAmountMST from the CustInvoiceTrans on the charts value field and put the InvoiceDate from the CustInvoiceTrans on the Axis field.

Power BI line chart

Fine-tuning

Give each chart a meaningful name. Change the size for the text to fit your report style. Switch to the data view. Change the column names into something more meaningful for an end user e.g. LineAmountMST to Amount. Change the columns formats e.g. Currency for LineAmountMST, date format for the InvoiceDate.