How to show the Average in the Total Line of a Table when using a Measure?

[ERing]

Using the sample data below, I have a requirement to show the Average Close Rate in the Total Line of a table visual rather than the current result which is the % Closed of Opportunities. In this example, the end users would want to see 57.2% in the Total line of the Close Rate because (58.5+50.3+48.5,+72.3+77.1+36.9+71.0+43.4 = 458)/(8) = 57.25%

State	Opportunities	Closed	Close Rate
OH	1,163	680	58.5%
OK	1,021	514	50.3%
OR	1,474	715	48.5%
PA	1,001	724	72.3%
RI	1,096	845	77.1%
SC	1,431	528	36.9%
SD	1,030	731	71.0%
TN	1,236	536	43.4%
Total	9,452	5,273	55.8%

[Ashish_Mathur]

Hi,

Try this measure

Measure = averagex(values(Data[State]),[close Rate])

Hope this helps.

[audreygerred]

Hello! I used the below and got 57.25%

Closed_Rt_Avg = AVERAGEX(Table,Table[Closed_Rate])

[ERing]

I think maybe I wasn't clear in my set up. The sample I posted is actually the table visual in my report. In the table visual I've brought in columns for opportunities, closed, and closed rate.

Opportunities = COUNT(OPPORTUNITY_ID)
CLOSED = CALCULATE ([OPPORTUNITIES],'SALES_TABLE' [SALES_STATUS] = "CLOSED")

CLOSED RATE = DIVIDE(Closed,Opportunities)

It's very possible I'm missing something, but I think the solution you provided only works if I already have a table of summarized data?

[audreygerred]

AVERAGEX is an iterator function that first sums each row then averages the sums: https://learn.microsoft.com/en-us/dax/averagex-function-dax#example