Help with Average in Totals row when using multiple measures across 3 tables

[Anonymous]

I'm really hoping someone can help. 

In my report I have to keep various tables in in their non aggregated format so that we can use filters on the criteria.  But I also need to serve a visual with aggregated calculations. This ordinarily wouldn't be a problem but one of my measures is a calculation based on two other measures  - each on a different table. 

You can see in my visual below that i have Gross Opens sum and Gross Clicks sum by EmailID. I also have a third column which is a rate based on Clicks / Opens. 

In the total row, I need to show the AVERAGE CTR.  You can see ini the example calculation in Excel vis the example from PBI the different results for the total Average for the CTR column

Example Calculation in Excel (CORRECT):

How PowerBI calculates (INCORRECT RATE_Gross CTR on Total row - should be AVG by aggregate emailID:

I hope this makes sense how I explained. 

I do have a simple PBI report I can share but i don't see a link to attach a file. 

[jdbuchanan71 · Accepted solution]

Hello @Anonymous 

I was able to get it to work by changing the summing mesaures to return 0 for blanks and using AVERAGEX:

Gross Opens = 
VAR 
    Opens = SUM (opens_table[open_count])
RETURN
    IF ( ISBLANK ( Opens ), 0 , Opens )

Gross Clicks = 
VAR 
    clicks = SUM ( clicks_table[click_count] )
RETURN
    IF ( ISBLANK ( clicks ) , 0 , clicks )

CTR = 
    AVERAGEX( emails , DIVIDE ( [Gross Clicks], [Gross Opens] ) )

[jdbuchanan71 · Accepted solution]

Hello @Anonymous 

I was able to get it to work by changing the summing mesaures to return 0 for blanks and using AVERAGEX:

Gross Opens = 
VAR 
    Opens = SUM (opens_table[open_count])
RETURN
    IF ( ISBLANK ( Opens ), 0 , Opens )

Gross Clicks = 
VAR 
    clicks = SUM ( clicks_table[click_count] )
RETURN
    IF ( ISBLANK ( clicks ) , 0 , clicks )

CTR = 
    AVERAGEX( emails , DIVIDE ( [Gross Clicks], [Gross Opens] ) )

[Anonymous · Accepted solution]

Oh my god. I can't believe you figured that out. It worked perfectly. I was going down the entirely wrong path with this one. 

Thank you so much for the help! I can't tell you how much I appreciate it!