Forum Discussion

bwanRPD's avatar
bwanRPD
Frequent Visitor
6 years ago
Solved

How to divide two different values on two different tables, by the related value on both tables?

Hi,

 

I have two tables that each has one field in common, for example, both tables have a column containing Color (see below example).

I want to be able to, in a Matrix, sum the size of Table 1 according to color, and then divide by the number of staff in Table 2. How could I accomplish this? Thank you

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

     

    bwanRPD 


    Try create this measure in Table 1:

    Result = 
var sumbycolor = CALCULATE(SUM(Table1[Size]),ALLEXCEPT(Table1,Table1[Color]))
var staffno = CALCULATE(SUM('Table2'[Staff]),FILTER(Table1,Table1[Color]=RELATED(Table2[Color])))
Return DIVIDE(sumbycolor,staffno)

     


    Best,
    Paul

9 Replies

  • az38's avatar
    az38
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi bwanRPD 

    try to add a measure into 'Table 1'

    Measure =
DIVIDE(
SELECTEDVALUE('Table 1'[size]);
CALCULATE(SUM('Table 2'[Staff]);'Table 2'[Color]=SELECTEDVALUE('Table 1'[Color]))
)

    do not hesitate to give a kudo to useful posts and mark solutions as solution

    • bwanRPD's avatar
      bwanRPD
      Frequent Visitor
      6 years ago

      Seems like I'm getting some issues with that

       

      A yellow popup says: The syntax for ';' is incorrect.

       

      Then when I hover over the measure it says: Unexpected expression 'calculate'

      • az38's avatar
        az38
        Icon for Community Champion rankCommunity Champion
        6 years ago

        bwanRPD 

        try to change ";"to ","

        it should be a system locale issue

        do not hesitate to give a kudo to useful posts and mark solutions as solution

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

     

    bwanRPD 


    Try create this measure in Table 1:

    Result = 
var sumbycolor = CALCULATE(SUM(Table1[Size]),ALLEXCEPT(Table1,Table1[Color]))
var staffno = CALCULATE(SUM('Table2'[Staff]),FILTER(Table1,Table1[Color]=RELATED(Table2[Color])))
Return DIVIDE(sumbycolor,staffno)

     


    Best,
    Paul