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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Need help to calculate simple average as grand total

Hi,

 

I need help on calculating simple average as grand total. Like example below, I want grand total to be (17%+50%+67%+30%) divided by 4 projects = 41%. I do not want ($7 divided by $21 = 33%). How do I put this as a measure?

 

P/S 'Return (%)' = 'Revenue ($)' divided by 'Expense ($)'

Capture.JPG

1 ACCEPTED SOLUTION
Anonymous
Not applicable

@Anonymous Please use below measure 

Simple Average = 
VAR _ratio = DIVIDE(SUM(data1[Revenue]),SUM(data1[Expenses]))
VAR _table = SUMMARIZE(data1,data1[Department Description],data1[Project],"total",DIVIDE(SUM(data1[Revenue]),SUM(data1[Expenses])))
RETURN IF(HASONEVALUE(data1[Project]),_ratio,AVERAGEX(_table,[total]))

View solution in original post

8 REPLIES 8
az38
Community Champion
Community Champion

Hi @Anonymous 

didnt you try to set Return Filed aggrageation as Average? will it be ok?

7312_1.png

7312_2.png

do not hesitate to give a kudo to useful posts and mark solutions as solution

 

 


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
Anonymous
Not applicable

Hi az38,

 

As Return% in itself is a measure, there is no such option to "Average" it when I right click. 

 

Grace

Anonymous
Not applicable

@Anonymous Please try below measure

Measure = AVERAGEX('Table',DIVIDE('Table'[Revenue],'Table'[Expense],0))
Anonymous
Not applicable

Hello @Anonymous ,

 

I tried using your formula but it returns the below. I would like Simple Average % to appear as 7.355% [(7.71% +7.00%)/2]. Any idea?

 

 

Upload1.JPG

Anonymous
Not applicable

@Anonymous Please refer the attached image I'm getting the correct average. can you please explain problem statement in more details. may be you can share some sample data along with expected result.total.png

Anonymous
Not applicable

Hi @Anonymous ,

 

I have uploaded the pbix with the sample dataset for your reference below.

https://drive.google.com/open?id=1YLAB1YnvS9SJ0M7jGxqs91mTsUeCk8x-

 

The part that I need to figure out is on the measure for Simple Average %. The simple average % at project levels are correctly displayed in the visual table (they should be the same as return % at project level). However, the grand total for simple average % is incorrect. I would like to show it as sum of simple average % at project level when filters are applied in visual table; divided by count of projects when filters are applied in visual table. Example would be like snapshot below.

Snapshot.JPG

Grace

Anonymous
Not applicable

@Anonymous Please use below measure 

Simple Average = 
VAR _ratio = DIVIDE(SUM(data1[Revenue]),SUM(data1[Expenses]))
VAR _table = SUMMARIZE(data1,data1[Department Description],data1[Project],"total",DIVIDE(SUM(data1[Revenue]),SUM(data1[Expenses])))
RETURN IF(HASONEVALUE(data1[Project]),_ratio,AVERAGEX(_table,[total]))
Anonymous
Not applicable

Thank you @Anonymous ! This works exactly like how I wanted it! Appreciate it!

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.