cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

How to Calculate Average of Total in Power BI

Dear All,

             How to Calculate Average Of Total in Power BI. for Example

ItemQuantityAvgOf Total -Sum(Quantity)/NoOfRows
Item1109
Item2129
Item399
Item489
Item569

 

this Exampe is 5 Item and Quantity is (10+12+9+8+6)=45 which devided is NoOfRows is 5 Total Values is 9 in every Rows. how to achieve this please suggest. 

1 ACCEPTED SOLUTION
Memorable Member
Memorable Member

Hi @vijayit43,

 

Use following steps:

 

1. Create a new measure.
2. Use below DAX. Replace Table1 with your table name.

AverageofTotal = 
VAR Countofrows = CALCULATE(COUNTROWS(Table1),ALL(Table1))
VAR TotalQuantity = CALCULATE(SUM(Table1[Quantity]),ALL(Table1))
RETURN
CALCULATE(DIVIDE(TotalQuantity,Countofrows,0),ALL(Table1[Item])
)

Solution1.PNG

Prateek Raina

 

View solution in original post

5 REPLIES 5
New Member

Just create a new measure with formula

Average = CALCULATE(AVERAGE('Table'['Column']),ALLSELECTED('Table'))

 

If you want to round the average to a certain number of digits, just do

Average = CALCULATE(ROUND(AVERAGE('Table'[Column]), 'Number of Digits' ),ALLSELECTED('Table'))

Frequent Visitor

Thank You jashan.

Memorable Member
Memorable Member

Hi @vijayit43,

Kindly upvote my answer if you find it as suitable solution.

 

Prateek Raina

Memorable Member
Memorable Member

Hi @vijayit43,

 

Use following steps:

 

1. Create a new measure.
2. Use below DAX. Replace Table1 with your table name.

AverageofTotal = 
VAR Countofrows = CALCULATE(COUNTROWS(Table1),ALL(Table1))
VAR TotalQuantity = CALCULATE(SUM(Table1[Quantity]),ALL(Table1))
RETURN
CALCULATE(DIVIDE(TotalQuantity,Countofrows,0),ALL(Table1[Item])
)

Solution1.PNG

Prateek Raina

 

View solution in original post

Capture.JPGdear prateek,

 

I want to take average amount to circle places only.in table when apply "total" to all fields.

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors