cancel
Showing results for
Did you mean:
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

3 REPLIES 3
Super Contributor

## Re: Average of Columns

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

Proud to be a Datanaut!

Frequent Visitor

## Re: Average of Columns

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 Contributor

## Re: Average of Columns

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

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.

Proud to be a Datanaut!

Announcements

#### Challenge: Can You Solve These?

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

#### Community News & Announcements

Get your latest community news and announcements.

#### 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.

#### Win Power BI Swag with Community Kudopalooza!

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

Top Kudoed Authors
Users Online
Currently online: 150 members 1,554 guests
Recent signins: