cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rax99 Member
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)

 

averagesum.JPG

 

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
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


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

Proud to be a Datanaut!

rax99 Member
Member

Re: How to SUM and then AVERAGE

Hi Greg,

 

Please follow link to data sample;

 

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

 

Thanks

Community Support Team
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]))

Capture.PNG

 

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.
rax99 Member
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
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
        )
    )

2.PNG

 

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

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

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

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.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

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

Users Online
Currently online: 40 members 1,006 guests
Please welcome our newest community members: