cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
heimk008 Frequent Visitor
Frequent Visitor

averages

I have combined sets up data (form submissions) from multiple marketing campaigns to create a master quiery of all form responses. I have graphs of total responses, Inquiries, & Leads and CVR broken down by each campaign. I am now being asked to add another set of columns that show the average across all of the campaigns. I cannot figure out a way to calculate this in general and I'm not sure if it is this even possible to include an "average" set within the same graph...

 

My data is all by individual email address of every submitted form and they become a Lead if they have "on" within a certain column. So, I have a column that says "INQ" or "MQL" based on if they have "on" where it should be.

 

Data looks something like this (but with a lot more columns):

Data.jpg

 

Here is the current graph and I'm being asked to add another grouping of bars next to "X-Well" that is the averages of them all

Graph.jpg

 

I currently have the following formulas/measures created: 

  • Total Responses = COUNTROWS(Submissions)
  • MQLs = CALCULATE([Total Responses],Submissions[INQ or MQL]="MQL")
  • Inquries Only = CALCULATE([Total Responses],Submissions[INQ or MQL]="INQ")
  • CVR = DIVIDE([MQLs],[Total Responses])

 

thanks for the help!

7 REPLIES 7
v-ljerr-msft Super Contributor
Super Contributor

Re: averages

Hi @heimk008,

Here is the current graph and I'm being asked to add another grouping of bars next to "X-Well" that is the averages of them all

I don't think there is an easy way to do it in Power BI currently. However, I believe it still could be done. Following is the solution in my head. Smiley Happy

 

To show another bar group of averages on your bar chart, an additional value(you can call it "Average") needs to be added to your campaigns, which means that you may need to add a new table with a column called "campaigns" that contains all the values of campaigns and the additional values "Average" to your model, and built a relationship with your original campaigns column.

 

And then you will need to use SWITCH function to create new measures to switch calculation for the campaigns and the "Average" accordingly.

 

Regards

heimk008 Frequent Visitor
Frequent Visitor

Re: averages

@v-ljerr-msft,

 

Thanks for the reply. I am still relatively new to Power BI, so trying to execute what you mentioned kind of goes over my head. Would you be able to try and help me walk through that in a bit more detail? Also, using this method will the data still be dynamic and update when I update the original reports? 

 

thanks,
Danielle

v-ljerr-msft Super Contributor
Super Contributor

Re: averages

Hi @heimk008,

Also, using this method will the data still be dynamic and update when I update the original reports? 

Yes, I think so.

 

Could you share a sample pbix file with just some sample/mock data, so that I can test on it and try to provide a more detailed solution? You can upload it to OneDrive or Dropbox and post the link here, or sent it to me in private message. Do mask sensitive data before uploading. Smiley Happy

 

Regards

heimk008 Frequent Visitor
Frequent Visitor

Re: averages

@v-ljerr-msft,

 

I have sent you a link through a direct message. I am actually looking to have averages for both of the left side graphs if possible. The top one does involve data from another soure, which you should be able to see within the quieries. 

v-ljerr-msft Super Contributor
Super Contributor

Re: averages

Hi @heimk008,

 

The link is not available to me. I got the following error when try to open it. Smiley Happy

 

issue.PNG

 

Regards

heimk008 Frequent Visitor
Frequent Visitor

Re: averages

I just sent you a new link from Dropbox. Hopefully that one will work! 

Highlighted
heimk008 Frequent Visitor
Frequent Visitor

Re: averages

@v-ljerr-msft

 

did you get the new link to work?