Calculation on table 2 reported in table 1

[Fab117]

Hi,

I'm looking to do a calculation on table 2 value (based on a code id) which should be reported in Table 1 (connected to Table 2 through the same code id).

To illustrate:

My sources:

Table 1 is a list of items needed at a defined date (a same item can be expected several times over the year => several lines for each code in Table 1):

Code	Date	Unit number
A	Jan	100
B	Jan	100
C	Feb	100
A	March	50
A	March	100
C	April	80
A	May	150
B	May	100

NB: Columns "Date" and "Unit number" are useless at this satge

Table 2 contains price for each code. Challenge is coming from several prices by item depending on number of units per item. In other words, in this Table 2 a code can be presented several times. Each time with a price for a certain number of units:

Code	Price for number of units considered	Number of units
A	5	1
A	22	5
B	4	1
C	6	1
B	17	5
A	9	2

NB: As you'll read below, I'm only interested by the average price for 1 unit

My expectation:

I would like to report in Table 1 the average price for the corresponding Code for each line

=> Targeted Table 1:

Code	Date	Unit number	Average price per unit
A	Jan	100	4.633333
B	Jan	100	3.7
C	Feb	100	6
A	March	50	4.633333
A	March	100
C	April	80	6
A	May	150	4.633333
B	May	100	3.7

For each code, average price is calculated based on Table 2 values as: Average of each "Price"/"Number of Units".

E.g. Average price per unit for Code "A" = [(5/1)+(22/5)+9/2)]/3 = 4. 633333

I've no idea if its simpler to:

• Do it in Power BI desktop or in Power Query
• Calculate the average price first in Table 2 (new column) and then only report the value in Table 1 or directly do the calculation in the new Table 1 column.

Thanks in advance looking at my challenge

Fab

[Fab117]

Hi,

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]

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 · Accepted solution]

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.

[Fab117]

@MFelix 

Big thank you for your help

Fab