How can I create this visual in Power BI?

[ERing]

I've been given a requirement to create the visual below in Power BI. The report will be used to display marketing leads to contacts conversion rates by marketing source.

I'm unsure how to accomplish the requirement to show the current period selection (red or green bars) vs then dynamically show prior period selection (grey bars). I'm also unsure how to incorporate the % change calculations shown in the horizontal row under the bar chart

Here is the list of requirements I've been given.

1. Report has a date filter to select the current period.
2. The previous period will be dynamically calculated using the same number of days previous to the date range selected in the date filter for the current period. For example, a user selected 6/16 - 6/30. This is a 15 day period so the previous period should show the data for the prior 15 days (6/1 - 6/15). If a user were to select 6/1 - 6/30 (30 days), the previous period would be the prior 30 days (5/2 - 5/31).
3. The % change from current period to previous period will be shown in the chart. The calculation for this is (current period - previous period)/(previous period).

I'm open to any and all creative suggestions.

My data is structured at a day level and for each day provides the marketing source, number of leads for that marketing source, and number of leads converted to contacts for that marketing source.

[v-agajavelly]

Hi @ERing ,

Thanks @Ashish_Mathur for the clear and helpful explanation.

@ERing  just to confirm, as suggested by super user did you get a chance to look into the issue with labels coloured. Would be great to hear if it worked for you.

Regards,
Akhil.

[ERing]

Hi @v-agajavelly  I have not found a way to display the percent change for the previous period to the current period. Any suggestions?

[Ashish_Mathur]

Hi,

Share the download link of an MS Excel file with data for atleast 2 months.  Also, clearly explain how you calculated 4% and 7.7% for E-mail.

[ERing]

Hi @Ashish_Mathur 
Here is the link Excel link with data for 6 months.

The 4% and 7.7% for Email in my example were just hard coded into a PowerPoint table to create the chart. The formula for Lead to Contact conversion = sum(Number of Leads Converted to Contacts)/sum(Number of Leads)

Thanks for your help

Excel Sample Data 

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.  In a clustered column chart, one cannot colour the columns conditionally so i have coloured the data labels instead.

Hope this helps.

[ERing]

@Ashish_Mathur Thanks! very helpful. Do you think it's possible to somehow display the percent change from previous period to current period?

Even if you have a suggestion for a technique I can look into myself.


From the example in my original post:

[Ashish_Mathur]

You are welcome.  Someone else will help you with that.