cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
Microsoft
Microsoft

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

@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

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

@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. 

Hi @heimk008,

 

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

 

issue.PNG

 

Regards

@v-ljerr-msft

 

did you get the new link to work? 

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

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors