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

Average of Columns

I have three fields with sales representative in one listing who was working that day, one being the date and then on a different data source I have the date field as well and everything that was sold that day.  I am trying to figure out what the average # of sold for each salesperson per days worked by item since everyone has not worked the same number of days.  My data looks something like what is below.  So I would find out the average # of gloves sold by Sally and the average # of shoes sold by Sally based on working 3 days.  Thank you for the help

             

 

 Data 1.PNGData 2.PNG

 

3 REPLIES 3
Super User
Super User

Re: Average of Columns

Hi @orischmann9291

 

Can you please post what your expected outcome is for this sample set of data?

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

orischmann9291 Frequent Visitor
Frequent Visitor

Re: Average of Columns

Hi @Phil_Seamark

 

In the sample data my expected output would be 

Sally average gloves per day = 1.66

Sally average shoes per day = .2

 

So it would be two measures but obviously once I know have the first measure it would be repalcing gloves for shoes to get the second one.  I want to be able to see what this is for everyone and there averages on both things.

Super User
Super User

Re: Average of Columns

Hi @orischmann9291

 

I added the following three calculated measures to my model using your sample data.

 

Days worked = CALCULATE(DISTINCTCOUNT('Table1'[Date]),ALLEXCEPT('Table1','Table1'[Salesperson]))
Items Sold = CALCULATE(COUNTROWS('Table2'))
Ratio = DIVIDE([Items Sold],[Days worked])

And got the following when used in a table visual

 

image.png

As you can see, I calculate that Sally worked for three days (1st, 5th & 6th), and she sold 4 gloves and 2 shoes in that time, so not sure what I am doing wrong to not arrive at your numbers.

 

I have attached a PBIX file for you to look at.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!