DAX (Power BI)- Averaging rates of children at a parent level

[kconner77]

HI, looking for help creating an average of two children rates at a parent level. here is the data...

Campaign ID	Email ID	Persona	Sends	Opens
ABC	1	Teacher	333	111
ABC	2	Teacher	222	111
ABC	3	Teacher	999	111
ABC	3	Admin	111	50

If I wanted a 'Total Rate' for Campaign ABC, I'd total all opens & divide by all Sends (383/1665 = 23.0%), easy to do in DAX.

What I need for an 'Average Rate' is the average of the 3 open rates for the children (the 3 emails, 33%, 50%, 14.5%; average = 32.3%).

How can I use DAX for Power BI to do this, since the Average function requires a column?

Was trying Calculate, Group By, and Summarize, but cannot seem to get there. Advice?

[Ashish_Mathur · Accepted solution]

Hi,

These measures work

O = SUM(Data[Opens])

S = SUM(Data[Sends])

Measure = AVERAGEX(SUMMARIZE(VALUES(Data[Email ID]),Data[Email ID],"A",[S],"B",[O]),DIVIDE([b],[a]))

[kconner77]

Thanks, worked great. Appreciate the help.

[lbendlin]

You need to materialize the measure if you want the average of its values across groups.

Return Rate averaged = 
var a = ADDCOLUMNS(SUMMARIZE('Table',[Campaign ID],[Email ID]),"Ret",calculate([Return rate simple]))
return averagex(a,[Ret])