Forum Discussion

sguenther's avatar
sguenther
Icon for Advocate II rankAdvocate II
10 years ago
Solved

Average Sales per Sales Rep

Hey guys,

 

hopefully this is just a simple DAX question:

 

Let's assume we have two tables. One with all Sales Reps:

 

Rep ID | Name

1          | Greg

2          | Susan

...

 

and another table with all sales made:

 

Date | Rep ID | Amount

...      | 1          | XXX $

...      | 2          | XXX $

...      | 1          | XXX $

 

The two tables are linked via the Rep ID. We also have a date table. Now I would like to know the average number of sales (amount sold) per sales rep per time. How would I write a measure for that?

 

Thanks in advance and best,

Sebastian

Need Help
  • sguenther's avatar
    sguenther
    10 years ago

    ok, so found the solution myself. In case you want to know:

     

    Define a measure that calculates the sales made per period:

    # Sales =
CALCULATE(
    COUNTROWS(sales),
    FILTER(sales,
        [Date] >= MIN(calendar[Date]) &&
        [Date] <= MAX(calendar[Date])
    )
)

    Then define a new measure for the average. I was interested in not only the average, but the median:

    Median # Sales per Rep =
CALCULATE(
    AVERAGEX/MEDIANX(sales, [# Sales]),
    FILTER(reps,
        [Rep ID] > 0
    )
    )

     

    Maybe this helps someone.

     

    Best,

    Sebastian

3 Replies

  • ankitpatira's avatar
    ankitpatira
    Icon for Community Champion rankCommunity Champion
    10 years ago

    sguenther If you have date column in sales table then you can go to query editor -> under Transform tab -> Group By. Using DAX you can use GROUPBY or SUMMARIZE function.

      • sguenther's avatar
        sguenther
        Icon for Advocate II rankAdvocate II
        10 years ago

        ok, so found the solution myself. In case you want to know:

         

        Define a measure that calculates the sales made per period:

        # Sales =
CALCULATE(
    COUNTROWS(sales),
    FILTER(sales,
        [Date] >= MIN(calendar[Date]) &&
        [Date] <= MAX(calendar[Date])
    )
)

        Then define a new measure for the average. I was interested in not only the average, but the median:

        Median # Sales per Rep =
CALCULATE(
    AVERAGEX/MEDIANX(sales, [# Sales]),
    FILTER(reps,
        [Rep ID] > 0
    )
        )

         

        Maybe this helps someone.

         

        Best,

        Sebastian