Forum Discussion
How can I create this visual in Power BI?
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.
- Report has a date filter to select the current period.
- 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).
- 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.
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.
7 Replies
- Ashish_Mathur
Super User
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
Post Partisan
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
Super User
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.
- AnonymousNot applicable
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
Post Partisan
Hi Anonymous I have not found a way to display the percent change for the previous period to the current period. Any suggestions?