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

[v-yuta-msft]

@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]

@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

[ryan_mayu]

@Anonymous

You can try below coding . Thanks

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

[Anonymous]

@ryan_mayu

Thanks for your reply.

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

[ryan_mayu · Accepted solution]

@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)

[Anonymous]

@ryan_mayu

Thanks for your reply.

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

[ryan_mayu]

@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

[Anonymous]

Hi Thanks for your reply. I already tried but didn't get the desired result.