Dynamic "Series" in a line chart

user32470

Hi Folks,

 

Let me try to explain this as clear as I can since the subject title is sort of not clear. For context, my role is card builder within my organization. I am putting together a dashboard that is related to SEM Competitive insights using google auction insights data.

 

This is my default view of my line chart. Here I have my company (green line) and our competition in blue. I used a simple case statement to make a beast mode that groups all our competitors into a "Competitor" group because there are over 50+ competitors which makes the visual look like a piece of abstract art. 

 

 

At the top of my dashboard, I have a simple filter that lists ALL our competitors (all the competitors in the competitor group). This is for someone to look at how a specific competitor is doing compared to us. Naturally, when you pick a specific competitor in the filter, it will remove our company/brand from the chart and only show the line chart for said competitor like this:

 

 

How would I make it so when someone decides to look at a specific competitor it will still keep our companys metrics in the same line chart? is it even possible?

GrantSmith

Hi @user32470 ,

 

You'd need to have two records for each competitor in your data set.

 

Assuming your data looks like this:

Date	Competitor	Amount
1/1/2020	ABC	500
1/1/2020	DEF	123
1/1/2020	Our Company	987

 

You'd want it structured like:

Date	Competitor	Comparison	Amount
1/1/2020	ABC	Them	500
1/1/2020	ABC	Us	987
1/1/2020	DEF	Them	123
1/1/2020	DEF	Us	987

 

This way they can filter on the Competitor but still plot the two different series (Comparison).

 

 

Alternatively you could utilize a data flow and select only your company's data do a join to the main dataset based on date and include the amounts from both tables so it'd look something like:

Date	Competitor	Them Amount	Us Amount
1/1/2020	ABC	500	987
1/1/2020	DEF	123	987
1/1/2020	Our Company	987	987

 

Then you'd use Them Amount and Us Amount as your two series and allow the user to filter on Competitor.

GrantSmith

Hi @user32470 ,

 

You'd need to have two records for each competitor in your data set.

 

Assuming your data looks like this:

Date	Competitor	Amount
1/1/2020	ABC	500
1/1/2020	DEF	123
1/1/2020	Our Company	987

 

You'd want it structured like:

Date	Competitor	Comparison	Amount
1/1/2020	ABC	Them	500
1/1/2020	ABC	Us	987
1/1/2020	DEF	Them	123
1/1/2020	DEF	Us	987

 

This way they can filter on the Competitor but still plot the two different series (Comparison).

 

 

Alternatively you could utilize a data flow and select only your company's data do a join to the main dataset based on date and include the amounts from both tables so it'd look something like:

Date	Competitor	Them Amount	Us Amount
1/1/2020	ABC	500	987
1/1/2020	DEF	123	987
1/1/2020	Our Company	987	987

 

Then you'd use Them Amount and Us Amount as your two series and allow the user to filter on Competitor.

user32470

That make sense! Really appreciate you taking the time to write that out!!