cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors