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 week and per factory

 

 

I have tried this DAX formula, but don't expected results.

 

KWH per Volume = 
DIVIDE(
Energy[KWH],
CALCULATE(SUM(Volume[volume]),ALLEXCEPT(Volume,Factories[factory]),ALLEXCEPT(Volume,datemaster[WEEKs])),
0
)

 

 

Rgds,

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.

13 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Thx it works perfectly!:smileyhappy:

  • 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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    One finding I did is that the result of the measure is different according the table where you created it.