Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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] ) )
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!
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] ) )
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |