cancel
Showing results for
Did you mean:
Member

How to SUM and then AVERAGE

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.

6 REPLIES 6
Super User

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

I have book! Learn Power BI from Packt

Proud to be a Datanaut!

Member

Re: How to SUM and then AVERAGE

Hi Greg,

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

Thanks

Community Support Team

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

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

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.
Highlighted
Member

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.

Announcements

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Get your latest community news and announcements.

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 40 members 1,006 guests
Recent signins: