Take average of rows of table using Sum

[ELW]

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:

Company	Sales
A	10
A	15
A	20
B	25
B	30
C	35
C	40
C	45
C	50
D	5
D	10
E	15

And the table I have would look like this:

Company	Share
A	15%
B	18%
C	57%
Average	30%

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%).

[amitchandak]

@ELW , Try like

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

[Ashish_Mathur · Accepted solution]

Hi,

You may download my solution file from here.

Hope this helps.

[ELW]

@Ashish_Mathur That worked perfectly!  Thank you so much!

[Ashish_Mathur]

You are welcome.

[mahoneypat]

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