Re: averages

heimk008 · ‎12-11-2017

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):

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 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!

v-ljerr-msft · ‎12-12-2017

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.

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 · ‎12-13-2017

@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 · ‎12-13-2017

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.

Regards

heimk008 · ‎12-14-2017

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