Forum Discussion

Fab117's avatar
Fab117
Helper IV
3 years ago
Solved

Calculation on table 2 reported in table 1

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

Need Help
  • MFelix's avatar
    MFelix
    3 years ago

    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.

5 Replies

  • Fab117's avatar
    Fab117
    Helper IV
    3 years ago

    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

    CodeAverage price
    A4.633333
    B3.7
    C6

     

    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's avatar
      MFelix
      Super User
      3 years ago

      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's avatar
        Fab117
        Helper IV
        3 years ago

        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