Forum Discussion

ERing's avatar
ERing
Icon for Post Partisan rankPost Partisan
1 year ago
Solved

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.

 

  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.

 




  • 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

  • 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's avatar
      ERing
      Icon for Post Partisan rankPost 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's avatar
        Ashish_Mathur
        Icon for Super User rankSuper 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.

         

  • Anonymous's avatar
    Anonymous
    Not 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's avatar
      ERing
      Icon for Post Partisan rankPost Partisan

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