Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
@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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |