Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Calculating an Average across two tables

Hi,

I'm struggling to calculate an average from data held in two tables and would really appreciate some help as I'm quite new to measures / DAX etc.

 

I have a table that holds records of meetings by sales teams and a second table that contains the head count for those teams and I would like to generate the average number of meetings based on headcount.

 

My table structure looks like this (simplified):

Table 1 = Activity

Team NameMeeting
Sales Team AMeeting 1
Sales Team AMeeting 2
Sales Team AMeeting 3
Sales Team AMeeting 4
Sales Team BMeeting 1
Sales Team BMeeting 2
Sales Team BMeeting 3
Sales Team BMeeting 4
Sales Team BMeeting 5

 

Table 2 = Headcount

Team NameHeadcount
Sales Team A2
Sales Team B3

 

I can generate a measure that gives me the count of meetings (I'm using DISTINCTCOUNT as there may be dupe records):

Total Activities = DISTINCTCOUNT('Activity'[Meeting])
but I'm struggling to retrieve the headcount and perform the average calculation.
 
Many Thanks for all your help!
Jon.
Need Help
  • v-angzheng-msft's avatar
    v-angzheng-msft
    4 years ago

    Hi, Anonymous 

    Try to create a measure:

    Measure = 
DIVIDE(
DISTINCTCOUNT(Activity[Meeting]),SELECTEDVALUE(Headcount[Headcount]))

    Result:

    Please refer to the attachment below for details.

    Hope this helps.

     

     

    Best Regards,
    Community Support Team _ Zeon Zheng


    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    4 years ago

    oftentimes Power BI can provide a graphical answer - measures are only required when that is not possible.  In your case you can make a small change to the data model to allow filtering of the dimension table from the fact table

    and then you can use a table or matrix visual to show the result.

     

     

     

     

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, Anonymous 

    Try to create a measure:

    Measure = 
DIVIDE(
DISTINCTCOUNT(Activity[Meeting]),SELECTEDVALUE(Headcount[Headcount]))

    Result:

    Please refer to the attachment below for details.

    Hope this helps.

     

     

    Best Regards,
    Community Support Team _ Zeon Zheng


    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.