topic Re: Caltulate an average in between two columns from different tables, based on a relationship. in DAX Commands and Tips

Caltulate an average in between two columns from different tables, based on a relationship.

Anonymous — Sun, 24 May 2020 16:47:33 GMT

Hi to everyone, i have a cuestion with this two tables:

1. A database with the information from Employees.

2. Sales report of each one

What i need is add a column to the table #1 with an average based on each ID employe taking the sales from the other table, if is possible add a rule that limits year by year, month to month i've been working on this for days and not having results, hope someone can know how i can work this.

Thank you very much for reading.

Re: Caltulate an average in between two columns from different tables, based on a relationship.

Greg_Deckler — Sun, 24 May 2020 17:32:44 GMT

Sounds like you need to create a column like:

Average Sales = AVERAGEX(RELATEDTABLE('Sales'),'Sales'[Sales])

You can also wrap RELATEDTABLE with a FILTER to filter down to just the rows you want like:

Average Sales = AVERAGEX(FILTER(RELATEDTABLE('Sales'),YEAR([Date]) = 2020),'Sales'[Sales])

If this is not what you are looking for, Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Re: Caltulate an average in between two columns from different tables, based on a relationship.

Anonymous — Mon, 25 May 2020 02:43:15 GMT

It's great thank you very much!!!!

Just a little additional cuestion how can i add another filtere that ignores "0" values this is in case that the employee isn't working that day

Thanks for reading.

Re: Caltulate an average in between two columns from different tables, based on a relationship.

Greg_Deckler — Mon, 25 May 2020 02:55:04 GMT

Sure Anonymous you could do something like:

Average Sales = AVERAGEX(FILTER(RELATEDTABLE('Sales'),YEAR([Date]) = 2020 && [Sales] <> 0),'Sales'[Sales])