Average Sales per Sales Rep

[sguenther]

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

[ankitpatira]

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

@ankitpatira is there no way doing this calculation via a measure?

[sguenther · Accepted solution]

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