Forum Discussion
Caltulate an average in between two columns from different tables, based on a relationship.
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.
Community ChampionSounds 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.
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.
- Greg_Deckler
Sure Anonymous you could do something like:
Average Sales = AVERAGEX(FILTER(RELATEDTABLE('Sales'),YEAR([Date]) = 2020 && [Sales] <> 0),'Sales'[Sales])