Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
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

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

8 REPLIES 8
v-yuta-msft
Community Support
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_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  

@Anonymous

 

You can try below coding . Thanks

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

 

c1.JPG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

@ryan_mayu

 

Thanks for your reply.

 

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

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

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

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.