cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## 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
Super User

@Anonymous

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)

Proud to be a Super User!

8 REPLIES 8
Community Support

@Anonymous,

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

Anonymous
Not applicable

@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
Super User

@Anonymous

You can try below coding . Thanks

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

Proud to be a Super User!

Anonymous
Not applicable

@ryan_mayu

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

Super User

@Anonymous

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)

Proud to be a Super User!

Anonymous
Not applicable

@ryan_mayu

I have now got the calculation done at source level rather than tinkering in Power BI. So working fine now.

Super User

@Anonymous

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

Proud to be a Super User!

Anonymous
Not applicable

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors