locked
Divide Using Data in Two Tables Linked One to Many RRS feed

  • Question

  • Hi All,

    I have 2 tables setup in PowerPivot with a relationship based on [Factory Code] thus creating a 1 to many relationship:

    I have been trying to write a Measure in Table 2 that will count the [Orders] per [Factory Code] and divide it by the number of [Engineers] for that [Factory Code] in Table 1. So something like:

    =DIVIDE(count(Table2[Order]),Table1[Engineers],0)

    I have tried many functions including: Related, Related Table, Calculate, Filter, All, Max, Min and combinations of these but I can't get it to work.

    I also tried creating a calculated column in Table 2 which looked up the [Engineers] in Table 1 but of course I end up with multiple occurances in table 2 and then it returns an error when I divide.

    I need to build the measure in table 2 becuase when I use the tables in PowerBI I want all of the slicers based on Table 2.

    There are 178 different [Factory Codes] so I am staying clear of: IF = ABC, type formulae.

    Some other details: Office 2013, PowerQuery and Pivot addins, O365 PowerBI.

    Any help or guidance would be very much appreciated.

    Kind Regards,

    Danny


    Thursday, January 18, 2018 6:14 PM

Answers

  • Hi,

    Get the Engineers value in a new column in table 2 with RELATED Function.

    And use this expression :

    :=DIVIDE(COUNT([Order]),DIVIDE(SUM([Engineers]),COUNT([Order]),0),0)


    Ousama EL HOR

    [If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click Answered "Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


     [User Page]     [MSDN Page]     [Blog]     [Linkedin]

    • Marked as answer by Dan74 Thursday, January 18, 2018 7:50 PM
    Thursday, January 18, 2018 7:29 PM

All replies

  • Hi,

    Get the Engineers value in a new column in table 2 with RELATED Function.

    And use this expression :

    :=DIVIDE(COUNT([Order]),DIVIDE(SUM([Engineers]),COUNT([Order]),0),0)


    Ousama EL HOR

    [If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click Answered "Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


     [User Page]     [MSDN Page]     [Blog]     [Linkedin]

    • Marked as answer by Dan74 Thursday, January 18, 2018 7:50 PM
    Thursday, January 18, 2018 7:29 PM
  • Brilliant!!! Thank you so much! I have been trying to get this to work for days!

    Thursday, January 18, 2018 7:52 PM