cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
careisin60
Frequent Visitor

Average a measure's results for a group

I've gotten rusty with DAX and this should be an easy formula, but I'm not asking it in the right way to get the right search.  

 

I have a function that calculates how many customers each team member has.  I'd like to create a measure that calculates the average customer count for the entire team so the manager can see how close the individual is to the team average.

 

Here's my data: 

careisin60_0-1645225214450.png

To make it easier to understand, here it is summarized:

careisin60_1-1645225255086.png

The measure [Count of Cust ID] is DISTINCTCOUNTNOBLANK('Customers'[Cust ID])

I want to create a measure that will tell me the average customers for the entire team.  So the average customer count for Jon Vander's team and Garven Dreis.  So the In Garven Dreis's team, the average customer count should be (6+3+3)/3 = 4.

 

I'm trying to create cards that the individual can look at.  So if Briggs Darklighter gets to the page and selects his name in the slicer, he'll have a card that tells him his total customers (6) and another card that says average team customer count (4)

1 ACCEPTED SOLUTION
v-cazheng-msft
Community Support
Community Support

Hi @careisin60 ,

You may try these Measures.

1 Measure that calculates total customer of selected member

 

CountCustForMember = 
CALCULATE (
    DISTINCTCOUNT ( Customers[Cust ID] ),
    ALLEXCEPT ( Customers, Customers[Team Member] )
)

 

 

2 Measure that calculates average team customer

 

AvgCustOfTeam = 
VAR CustCountByLeader =
    CALCULATE (
        COUNT ( Customers[Cust ID] ),
        FILTER (
            ALL ( Customers ),
            Customers[Team Leader] = SELECTEDVALUE ( Customers[Team Leader] )
        )
    )
VAR CountMember =
    CALCULATE (
        DISTINCTCOUNT ( Customers[Team Member] ),
        FILTER (
            ALL ( Customers ),
            Customers[Team Leader] = SELECTEDVALUE ( Customers[Team Leader] )
        )
    )
VAR Result =
    ROUNDDOWN ( DIVIDE ( CustCountByLeader, CountMember ), 0 )
RETURN
    Result

 

 

Then, the result will look like this.

vcazhengmsft_0-1645691036502.png

 

Also, attached the pbix file as reference.

 

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!

 

Best Regards,

Community Support Team _ Caiyun

View solution in original post

5 REPLIES 5
v-cazheng-msft
Community Support
Community Support

Hi @careisin60 ,

You may try these Measures.

1 Measure that calculates total customer of selected member

 

CountCustForMember = 
CALCULATE (
    DISTINCTCOUNT ( Customers[Cust ID] ),
    ALLEXCEPT ( Customers, Customers[Team Member] )
)

 

 

2 Measure that calculates average team customer

 

AvgCustOfTeam = 
VAR CustCountByLeader =
    CALCULATE (
        COUNT ( Customers[Cust ID] ),
        FILTER (
            ALL ( Customers ),
            Customers[Team Leader] = SELECTEDVALUE ( Customers[Team Leader] )
        )
    )
VAR CountMember =
    CALCULATE (
        DISTINCTCOUNT ( Customers[Team Member] ),
        FILTER (
            ALL ( Customers ),
            Customers[Team Leader] = SELECTEDVALUE ( Customers[Team Leader] )
        )
    )
VAR Result =
    ROUNDDOWN ( DIVIDE ( CustCountByLeader, CountMember ), 0 )
RETURN
    Result

 

 

Then, the result will look like this.

vcazhengmsft_0-1645691036502.png

 

Also, attached the pbix file as reference.

 

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!

 

Best Regards,

Community Support Team _ Caiyun

tamerj1
Super User
Super User

@careisin60 

Would like to show the average instead of the subtotal?

Yeah, I want Jon Vander to be able to talk to Davish Krail about being below the average for the team and Keyan Farlander about being above the team average.

Hi @careisin60 ,
Here is the file with the solution https://www.dropbox.com/t/OGE09jZvurSG1nAI
The code is simple 

Count of Cust ID = 
VAR CustSum =
    DISTINCTCOUNTNOBLANK ( Customers[Cust ID] )
VAR NumberOfMembers =
    COUNTROWS ( VALUES ( Customers[Team Member] ) )
VAR Result = 
    DIVIDE ( CustSum, NumberOfMembers )
RETURN
    Result
Russell-PBI
Resolver II
Resolver II

Hi @careisin60, something like this?:

Average Customers = 
VAR TeamLeader = MAX('Table'[Team Leader])
VAR TeamLeaderCustomerCount = CALCULATE(
[Count of Cust ID]
, ALL('Table'[Team Member])
, 'Table'[Team Leader] = TeamLeader
)
VAR TeamMemberCount = CALCULATE(
DISTINCTCOUNT('Table'[Team Member])
, ALL('Table'[Team Member])
, 'Table'[Team Leader] = TeamLeader
)
RETURN
DIVIDE(TeamLeaderCustomerCount, TeamMemberCount)

EDIT: I was correct the first time

 

 

 

 

 

 

 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors