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
ELW
Advocate II
Advocate II

Take average of rows of table using Sum

Is it possible to have the total in a table average the values when sum aggregation is used?

 

I have a table basically showing share of sales (%) by company.  I'm using Sum aggregation and displaying the value as a percent of the grand total.  In the underlying data, there are multiple records per company that need to be totalled.  I'm also using a Top N filter, if that's relevant.  My question is, can I change the total row to take the average of the % share for each line?  

 

For example, the underlying data looks like this:

CompanySales
A10
A15
A20
B25
B30
C35
C40
C45
C50
D5
D10
E15

 

And the table I have would look like this:

CompanyShare
A15%
B18%
C57%
Average30%

 

The total by default shows 100% since it's showing the percent of the grand total, and I want it to show the average of each line.  In this case 30% = average(15%,18%,57%).

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

You may download my solution file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

5 REPLIES 5
amitchandak
Super User
Super User

@ELW , Try like

averageX(summarize(Table, Table[company], "_1",SUM ( Table[Sales] ),"_2", CALCULATE(SUM(]Sales[Sales]),ALL(Sales))),[_1])
Ashish_Mathur
Super User
Super User

Hi,

You may download my solution file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur That worked perfectly!  Thank you so much!

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
mahoneypat
Employee
Employee

Please try this expression in a measure to get your desired result in a table visual with the Company column.

 

Avg =
VAR __overalltotal =
    CALCULATE ( SUM ( Sales[Sales] ), ALL ( Sales ) )
RETURN
    AVERAGEX (
        VALUES ( Sales[Company] ),
        CALCULATE ( SUM ( Sales[Sales] ) ) / __overalltotal
    )

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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.