Forum Discussion
Help with Average in Totals row when using multiple measures across 3 tables
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.
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] ) )
2 Replies
- jdbuchanan71
Super User
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] ) )