cancel
Showing results for
Did you mean:
Regular Visitor

## Average of a measure based on Region

Hi I have a measure that calculates TotalProducts and I would like to now calculate Average([TotalProducts]) based on Region column. Can someone please help on how to get this. Please see below measure calculation

CALCULATE( CALCULATE( DISTINCTCOUNT( 'Product'[ProductNumber] ), FILTER( 'Product', ISBLANK( 'Product'[Modified Date]) = FALSE() ),
USERELATIONSHIP(DateTable[Date], 'Product'[Created Date]))+0)

When I do Average([TotalProducts]) - gives me error that TotalProducts cannot be used in this expression

1 ACCEPTED SOLUTION

Accepted Solutions
Established Member

## Re: Average of a measure based on Region

@pbiangel_7181

Should be the same way. I am not sure if below coding can meet your requirement. You can try this.

average = Divide(calculate(Regioncount,all(table)),Regioncount)

8 REPLIES 8
Established Member

## Re: Average of a measure based on Region

@pbiangel_7181

Could you plese try to add a region count measure?

Regioncount= distinctcount ('Product'[Region])

Average= Divide (TotalProducts, Regioncount)

Hope this is helpful for you. Thanks

Community Support Team

## Re: Average of a measure based on Region

Based on your description, formula TotalProducts is the formula below you have posted, right?

TotalProducts =
CALCULATE (
CALCULATE (
DISTINCTCOUNT ( 'Product'[ProductNumber] ),
FILTER ( 'Product', ISBLANK ( 'Product'[Modified Date] ) = FALSE () ),
USERELATIONSHIP ( DateTable[Date], 'Product'[Created Date] )
)
+ 0
)

If the formula is measure, change it into calculate column and try again.

Regards,

Jimmy Tao

Regular Visitor

Regular Visitor

## Re: Average of a measure based on Region

@v-yuta-msft

Thanks for your reply. I would like try using measures rather than custom column. This is what I need.

 Contact_address1_city RegionCount Average Calc NULL 5535 2.312195 =12798/5535 London 1252 10.22204 =12798/1252 HIGH WYCOMBE 554 23.10108 =12798/554 GLASGOW 619 20.67528 NOTTINGHAM 611 20.94599 Bristol 528 24.23864 BIRMINGHAM 515 24.85049 KING 249 51.39759 sunderland 460 27.82174 NEWCASTLE UPON TYNE 457 28.00438 LEEDS 437 29.28604 WREXHAM 430 29.76279 SHEFFIELD 393 32.56489 LIVERPOOL 390 32.81538 Manchester 368 34.77717 TotalProducts 12798
Established Member

## Re: Average of a measure based on Region

@pbiangel_7181

You can try below coding . Thanks

average = DIVIDE(sum(Table1[count]),CALCULATE(SUM(Table1[count]),ALL(Table1)))

Regular Visitor

## Re: Average of a measure based on Region

@ryan_mayu

RegionCount is a measure not column so the below wouldn't work.

Established Member

## Re: Average of a measure based on Region

@pbiangel_7181

Should be the same way. I am not sure if below coding can meet your requirement. You can try this.

average = Divide(calculate(Regioncount,all(table)),Regioncount)

Regular Visitor

@ryan_mayu