Is it possible to show 2 lines in the same OBIEE graph, one for selected and other for all

A few days back one of my friends came up with a problem in OBIEE, is it possible to show a line graph having 2 lines in the same graph. One line representing Revenue by a selected Sales Person and another line as the average Revenue by the remaining Sales Persons.

This might be achieved through different ways, but what I preferred was to using a Presentation Variable.

1. I have created a union report, the 1st criteria contains, Month and Avg Revenue. 'All Sales Person' is a hardcoded dummy column.

Avg Revenue is coming directly from a fact, and it is a simple Average on Revenue.

2. The 2nd criteria contains Month and Avg Revenue with Filter in the formula.

I am using the formula = FILTER("Base Facts"."Avg Revenue" USING ("Sales Person"."E1  Sales Rep Name" IN ('@{var_SP}')))

Basically it will filter the Avg Revenue for a Sales Person, and the value of the Sales Person will be passed through the Presenation Variable var_SP.

And in the dummy column I put '@{var_SP}'. This will help me show the name of the selected Sales Person in the line graph.

3. Next I have created a Prompt to filter the Year and Sales Person (to pass the value for var_SP)

4. I have put them all together in the Dashboard. When I first run the report without any Sales Person selected, it shows me only the Avg Revenue for all Sales Persons.

But the moment I select a specific Sales Person, an additional line appears.

Now it is time to validate, if my report is giving right data or not. And for that purpose I have created 2 simple seperate reports with Month, Avg Revenue and Month, Sales Person (Aurelio Miranda only) and Avg Revenue.

The result is matching with the chart, and that validates the Line chart report.

Integrating R with Power BI

I am fairly new to both Power BI and R. I know Power BI can be easily integrated with R, and there is Visualization available for that also. Therefore it would be great if can use both R and Power BI together.

First, I have checked that if my R script options are proper or not. Here I have set my R home directory and R IDE. With respect to R IDE (Integrated Development Environment), I will be explaining later.

Just to confirm I have 2 R directories and in this case I am going to use the 3.4.2, and the path correctly set in Power BI.

Next I add the R visual in Power BI.

It might ask you to enable it.

Now once it is added, there will be a R script editor available at the bottom.

For the purpose of the demo, I am using one csv file, I have received from a R training from Udemy (to confirm this is purely for demo purpose).

This csv contains 3 columns: carat (Diamond Carat), clarity (Diamond Clarity) and price (Diamond Price). Our objective is to prepare a visualization, to depict if the price is always proportional with the Carat and Clarity.

I add these 3 columns in the values for the R visualization, and you can notice a Dataset cotaining the columns gets created automatically.

Next for the purpose of my visualization I un-summarize carat and price. These are numeric field, and Power BI adds the aggregation by default.

Now before I start writing the code in the editor, let me go back to R IDE. There is a option on the top right of the editor, to open it is R IDE. What it will do is open the R Studio (as that is set as the R IDE for me), and I can do the coding in R Studio.

Going back to the objective creating a visualization to depict if the price is always proportional with the Carat and Clarity.

I have this piece of code in R Studio, which use ggplot, to create this chart.

Now I just copy and paste this code in the R Script Editor in Power BI. I just do minor change in the dataset portion.

When I run the code I get the same chart. And the best part is now I can use the additional Power BI capabilities like Slider etc with this.

Using D3.js

A few days back, for the first time, I heard about D3.js, and was really impressed with types and options of data visualizations it provides.

To start with what is D3.js?
D3 stands for Data-Driven Documents. It is JavaScript library for manipulating documents based on data. It lets you build the data visualization framework that you want, and bring data to life using HTML, SVG and CSS.

Who has developed it?
Mike Bostock wrote D3.js based on his work during his PhD studies at the Stanford Visualization Group.

Where to find D3.js library and sample codes?
D3.js library and sample codes can be found at https://d3js.org 

How D3.js works?
D3.js helps you attach your data to DOM (Document Object Model) elements. Then you can use CSS3, HTML, and/or SVG showcase this data. Finally, you can make the data interactive through the use of D3.js data-driven transformations and transitions.
When to use D3.js?
You should use D3.js because it lets you build the data visualization framework that you want. Graphic / Data Visualization frameworks make a lot of decisions to make the framework easy to use. D3.js focuses on binding data to DOM elements.
D3.js is written in JavaScript and uses a functional style which means you can reuse code and add specific functions to your heart's content. Which means it is as powerful as you want to make it. How you chose to style, manipulate, and make interactive the data is up to you.
You should use D3.js when your webpage is interacting with data, as it is a javascript library added to the front-end of your web application. Your back-end (the server) will generate the necessary data. The part of the application the users interact with (the front-end) will use D3.js.

Few samples,

Accessing Oracle Database Cloud Service using Putty