How to SUM and then AVERAGE

[rax99]

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.

[v-frfei-msft]

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

[rax99]

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.

[v-frfei-msft]

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

[rax99]

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.

[Greg_Deckler]

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

[rax99]

Hi Greg,

Please follow link to data sample;

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

Thanks