Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Divide 2 different columns from different tables and linked with 2 relations

Hi All, I want to calculate the KWH per Volume -the 2 colums are in deffernt tables -The tables are linked by relationships trough intermediate tables -the expected KWH per Volume shoud be per we...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    8 years ago

    Hi,

     

    In your visual, drag week from the Datamaster table and factory from the Factories table.  Then this measure should work

     

    =SUM(Energy[KwH])/SUM(Volume[Volume])

     

    If this does not help, then share the link from where i can download your file.

v-sihou-msft's avatar
v-sihou-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

Anonymous

 

Based on your screenshot, both Volume and Energy are fact tables, Week and Factory are dimension tables. Since both volumn and KWH are sliced by Week and Factory, you should be able to directly have KWH divided by volume.

 

=DIVIDE(Energy[KWH],RELATED(Volume[volume]))

Or you can lookup corresponding volume into Energy table. 

 

=LOOKUPVALUE(Volume[volume],Volume[factory],Energy[factory],Volume[Week],Energy[Week])

Then use KWH column divied by above lookup column.

 

Regards,

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Merci v-sihou-msft for your answer,

    however I still got errors:

     

    =DIVIDE(Energy[KWH],RELATED(Volume[volume]))

    returns : The column 'Volume[volume]' either doesn't exist or doesn't have a relationship to any table available in the current context.

     

     

    and

    LOOKUPVALUE(Volume[volume],Volume[factory],Energy[factory],Volume[Week],Energy[Week])

     

     

     returns:

    A single value for column 'factory' in table 'Energy' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

     

    is it possible with the 2nd option to returns a table and then sum up to have the total volume. So that the division can happen?