Average of a measure based on Region

[Anonymous]

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

[Ashish_Mathur]

Hi,

Try this measure

=AVERAGEX(VALUES(Product[Region]),[TotalProducts])

[v-piga-msft]

Hi @Anonymous,

For your requirement, you could try the quick measure in Power BI firstly.

If you still need help, please share some data sample and your desired output so that we could help further on it.

Best  Regards,

Cherry

[Anonymous]

Hi Thanks for your reply. I have tried Averagex and Quick Measure already. What I need is as below

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

[v-piga-msft]

Hi @Anonymous,

What about this measure?

Measure =
CALCULATE ( SUM ( Table1[RegionCount] ), ALLSELECTED ( Table1 ) )
    / CALCULATE ( SUM ( Table1[RegionCount] ) )

Best Regards,

Cherry

[Anonymous]

@v-piga-msft

RegionCount is a measure so wouldn't allow me to place the measure. Requires column.

[v-piga-msft · Accepted solution]

Hi @Anonymous,

OK, please try this.

Measure = SUMX(ALLSELECTED(Table1),[RegionCount])/[RegionCount]

If you still need help, please share more data sample which could reproduce your scenario and share the formula which is related so that we could help further on it.

Best Regards,

Cherry