Forum Discussion
Helper IVCalculation on table 2 reported in table 1
Hi Fab117 ,
To add this has a column you need to use the following sintax:
Average Price = AVERAGEX ( FILTER('Table 2', 'Table 2'[Code] = 'Codes Table'[Code]), DIVIDE('Table 2'[Price for number of units considered] , 'Table 2'[Number of units]) )You can also do this directly on the table 1 instead of having this bridge table if the values on table 2 are only to calculate the average price.
File attach.
Helper IVHi,
Still looking how to solve this issue.
I looked at options to decompose the work:
Option 1 I looked at:
Creating a new table (Table 3) for average price calculationwhere:
in 1st column a query would list all different "Code" found in 1st column of table 2 =>
| Code |
| A |
| B |
| C |
in 2nd column I looked at a new query which would take code found in first column and then calculate the average from Table 2
| Code | Average price |
| A | 4.633333 |
| B | 3.7 |
| C | 6 |
Option 2 I looked at:
Adding a column directly in table 2 where average hits are calculated. Then transfer new result content in Table 1 new column through Lookupvalue
I found tuto to calculate average as Measure, but not in Power Query (link)
I created a demo file to may be help understanding my challenge.
As I don't have sufficient rights to publish it as attached file to this post, I used the wetransfer service.
Link to download my demo file.
Thanks in advance for your support
- MFelix
Super User
Hi Fab117 ,
In this case the best option is to create a bridge table between both tables and make a relationship like this:
Now add the following measure:
Average Prices = AVERAGEX ( 'Table 2', DIVIDE('Table 2'[Price for number of units considered] , 'Table 2'[Number of units]) )Use the code from the bridge table and the result is below:
- Fab117
Hi MFelix ,
Thank you very much spending time looking at my issue.
Being a beginner with Power BI, I really appreciated the demo file.
I've been able to replicate the "Codes Table" it on my case.
As next step you propose a measure for the average price calculation. But it's difficult for me as I've several calculation that I didn't report in my example where it would be really useful to have either in "Table 1" or in "Codes Table" the average price.
I tried to add them in "Codes Table", ading a new column.
First with the formulae
Average Price = average('Table 2'[Price for number of units considered])/average('Table 2'[Number of units])But of course, I only get one result which the average of all table 2, without segregation on Code
Then I tried to use the LookUpValue formulae including a calculation
Average Price 2 = LOOKUPVALUE(xxcalculationxx,'Table 2'[Code],'Codes Table'[Code]) calculation based on average('Table 2'[Price for number of units considered])/average('Table 2'[Number of units])but it failed.
Any alternative to get average price in "Codes Table"?
Tks in advance
Fab
- MFelix
Hi Fab117 ,
To add this has a column you need to use the following sintax:
Average Price = AVERAGEX ( FILTER('Table 2', 'Table 2'[Code] = 'Codes Table'[Code]), DIVIDE('Table 2'[Price for number of units considered] , 'Table 2'[Number of units]) )You can also do this directly on the table 1 instead of having this bridge table if the values on table 2 are only to calculate the average price.
File attach.