## Desktop

Highlighted
Member
Posts: 68
Registered: ‎06-22-2018

# How to SUM and then AVERAGE

[ Edited ]

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.

Super User
Posts: 10,478
Registered: ‎07-11-2015

## Re: How to SUM and then AVERAGE

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

Proud to be a Datanaut!

Member
Posts: 68
Registered: ‎06-22-2018

## Re: How to SUM and then AVERAGE

[ Edited ]

Hi Greg,

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

Thanks

Community Support Team
Posts: 1,907
Registered: ‎07-10-2018

## Re: How to SUM and then AVERAGE

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Member
Posts: 68
Registered: ‎06-22-2018

## Re: How to SUM and then AVERAGE

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.

Community Support Team
Posts: 1,907
Registered: ‎07-10-2018

## Re: How to SUM and then AVERAGE

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Member
Posts: 68
Registered: ‎06-22-2018

## Re: How to SUM and then AVERAGE

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.