- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

# How to SUM and then AVERAGE

[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-21-2018 03:55 AM - edited 09-21-2018 04:15 AM

I have 2 tables;

**AgentDetails**

- TeamID

- DeptID

(TeamID M-to-1 DeptID)

**Sales**

-PermRev

-TempRev

-TotalRev = (PermRev + TempRev)

I have the following dax;

AvgDepMonthlySales = VAR SelectedTeam = SELECTEDVALUE(AgentDetails[TeamID]) VAR SelectedDepartment = LOOKUPVALUE(AgentDetails[DeptID],AgentDetails[TeamID],SelectedTeam) RETURN CALCULATE(AVERAGE(Sales[TotalRev]),ALL(AgentDetails), AgentDetails[DeptID]=SelectedDepartment)

The idea is that I compare the selected Team SUM of Sales (consists of TempRev - Dark blue, PermRev - Light Blue) for the month, against the average, SUM sales of the entire department on the line series.

But this isnt working, as it looks like the dax is either SUMming all the sales across all teams in the dept, or avaeraging the sales across all the teams. And not SUM then AVERAGE, which is what I want to do.

Worth mentioning I have a measure in the above DAX which is TotalRev (PermRev + TempRev)

Let me know if further clarification is required as Im not sure if I explained myself correctly.

## Re: How to SUM and then AVERAGE

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-21-2018 06:18 AM

Right, your ALL is what is probably messing you up. But, sample data would help to confirm. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

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

Proud to be a Datanaut!

## Re: How to SUM and then AVERAGE

[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-21-2018 08:02 AM - edited 09-21-2018 08:03 AM

Hi Greg,

Please follow link to data sample;

https://www.dropbox.com/s/dcydgycmh6x9eo3/sampleReport.pbix?dl=0

Thanks

## Re: How to SUM and then AVERAGE

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-23-2018 07:54 PM

Hi @rax99,

I create a measure as below in your pbix that you shared.

AvgDepMonthlySales1 = IF(ISBLANK([TotalSales]),BLANK(), AVERAGEX(ALLSELECTED(Sales),[TotalSales]))

For more details, please check the pbix as attached. If it doesn't meet your requirement, kindly share your excepted result to me.

Regards,

Frank

If this post

**helps**, then please consider

**to help the other members find it more quickly.**

*Accept it as the solution*## Re: How to SUM and then AVERAGE

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-24-2018 03:56 AM

Hi Frank,

Thanks for your input, but unfortunalty this does not meet requirements.

The measure you have created I beleive is an average over the entire dataset, so its appearing uniform from one month to another, this of course is incorrect.

Hopefully I can clarify my request;

Team A, Team B, Team C belongs to **DeptA**

Team D, Team E, Team F belongs to **DeptB**

Say I have Team A selected in the filter, I need to be able to see total/SUM of all sales [TotalRev] from Team A as bars on the chart. On the line series I need to be able to see an Average of the TotalRev from all the other teams in DeptA, so a sum of the TotalRev of each Team B, and Team C, and then an average of the two.

The idea is that I compare the total sales of Team A against the average total sales of all the other teams in the department.

I hope that helps.

## Re: How to SUM and then AVERAGE

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-25-2018 12:36 AM

Hi @rax99,

Please refer to the following steps.

1. Create two calculated column based in Sales table.

depthid = RELATED(AgentDetails[DeptID])

teamid = RELATED(AgentDetails[TeamID])

2. Create a measure to achieve your goal.

AvgDepMonthlySales1 = VAR Team = SELECTEDVALUE ( AgentDetails[TeamID] ) VAR sumsel = CALCULATE ( SUM ( Sales[TotalRev] ), FILTER ( ALL ( Sales ), Sales[teamid] = Team ) ) VAR depthid = CALCULATE ( MAX ( AgentDetails[DeptID] ), FILTER ( AgentDetails, AgentDetails[TeamID] = Team ) ) VAR sumall = CALCULATE ( SUM ( Sales[TotalRev] ), FILTER ( ALL ( Sales ), Sales[depthid] = depthid ) ) RETURN IF ( ISBLANK ( [TotalSales] ), BLANK (), DIVIDE ( sumall - sumsel, CALCULATE ( DISTINCTCOUNT ( AgentDetails[TeamID] ), FILTER ( ALL ( AgentDetails ), AgentDetails[DeptID] = depthid ) ) - 1 ) )

For more details, please check the pbix as attached.

Regards,

Frank

If this post

**helps**, then please consider

**to help the other members find it more quickly.**

*Accept it as the solution*## Re: How to SUM and then AVERAGE

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-27-2018 02:47 AM

Unfortunatly this still does not work. For example by just looking at the secondary axis your chart the figures are in the $1000s wheras the selected teams total sales for the month are in the $100s, I would expect the average for the department should be very similar. And also the average dept sales shouldnt be identical every month (as per the staright line on your chart).

Im not sure if your DAX is performinga SUM and AVERAGE across the entire dataset and therefore excluding all monthly filters which is why its appearing uniform from one month to another.