Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Super User
Super User

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
Anonymous
Not applicable

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
Super User
Super User

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.

Anonymous
Not applicable

Thank you, that does explain it! 

parry2k
Super User
Super User

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

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.