cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
pbiangel_7181 Regular Visitor
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
ryan_mayu Established Member
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
ryan_mayu Established Member
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
Community Support Team

Re: Average of a measure based on Region

@pbiangel_7181,

 

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

 

 

pbiangel_7181 Regular Visitor
Regular Visitor

Re: Average of a measure based on Region

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

pbiangel_7181 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_cityRegionCountAverageCalc
NULL55352.312195 =12798/5535
London125210.22204 =12798/1252
HIGH WYCOMBE55423.10108 =12798/554
GLASGOW61920.67528 
NOTTINGHAM61120.94599 
Bristol52824.23864 
BIRMINGHAM51524.85049 
KING24951.39759 
sunderland46027.82174 
NEWCASTLE UPON TYNE45728.00438 
LEEDS43729.28604 
WREXHAM43029.76279 
SHEFFIELD39332.56489 
LIVERPOOL39032.81538 
Manchester36834.77717 
TotalProducts12798  
ryan_mayu Established Member
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)))

 

c1.JPG

pbiangel_7181 Regular Visitor
Regular Visitor

Re: Average of a measure based on Region

@ryan_mayu

 

Thanks for your reply.

 

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

ryan_mayu Established Member
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)

pbiangel_7181 Regular Visitor
Regular Visitor

Re: Average of a measure based on Region

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