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
Phil_Seamark Super Contributor
Super Contributor

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.

Phil_Seamark Super Contributor
Super Contributor

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!

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 150 members 1,554 guests
Please welcome our newest community members: