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

Discrepancy in totals for averages

I am working on analyzing email metrics; I used to do a lot of this work in pivot tables in Excel, and now I am beginning to learn PowerBI to create dynamic dashboards to share data more regularly.

 

My main KPIs are open rate (number of opens/number of emails sent) and click rate (number of unique clicks/number of emails sent). I run my metrics quarterly, and I have 9 quarters worth of data. I was testing my ability to create Measures, and now I see a discrepency in the "Total" section of this table.

 

 

pbix avg discrepency.png

 

The columns Open Rate and Click Rate are from my table. I import each email sent as a row, with a column for number of emails sent (Audience Size), Opens, and Clicks. I created a column in this table to calculate open rate, and another for click rate, which I've displayed in the table above.

 

kehutchinson_0-1624286614584.png

 

For the FYQ Average Open Rate, I tested a Measure to pull that same information, borrowing the syntax from this post.

 

FYQ Avg Open Rate = 

CALCULATE (

AVERAGE ( 'Email Data'[Open Rate] ),

FILTER ( ALLSELECTED ( 'Email Data' ), 'Email Data'[FYQ] = MAX ( 'Email Data'[FYQ] ) )

)

 

As far as I can tell, I adapted this correctly; the table I've created shows they reach the same answer for each FYQ. However, at the bottom, the totals are different, and I don't understand why. I do keep track of our Cumulative open and click rates, as another important KPI, and I'm wondering if I've been calculating that KPI wrong for the past 8 quarters. Our goal this year for open rate is 75%, so that discrepency matters. Can someone explain why those totals are different, if the numbers in the actual table are the same for both types of column?

 

 

1 ACCEPTED SOLUTION
AlexisOlson
Community Champion
Community Champion

Notice that your total of 75.2% matches the average for the maximal date FY22 Q1 since that's exactly what your measure is written to do. For each of the rows, there is only one FYQ so MAX(FYQ) is just that single FYQ, so things are the same until you get the Total row which involves multiple FYQ.

View solution in original post

4 REPLIES 4
kehutchinson
Frequent Visitor

Thank you for the simplification. I set it up that way following the example I cited in another community post. I'm still quite new to the logic of the formulas since I've been letting pivot tables in excel do most of the work for me, so I like to find other examples to apply to my data. 

AlexisOlson
Community Champion
Community Champion

Notice that your total of 75.2% matches the average for the maximal date FY22 Q1 since that's exactly what your measure is written to do. For each of the rows, there is only one FYQ so MAX(FYQ) is just that single FYQ, so things are the same until you get the Total row which involves multiple FYQ.

View solution in original post

Thank you, that does explain it! 

parry2k
Super User III
Super User III

@kehutchinson not sure why you have measure the way it is, it should be straight Average

 

FYQ Avg Open Rate =  AVERAGE ( 'Email Data'[Open Rate] ),

 

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors