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 a...
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

bwanRPD's avatar
bwanRPD
Frequent Visitor
6 years ago

Syntax is correct but it's not showing any values.

 

I want to be able to pull this new measure into the values section of a Matrix visualization, and it would give me the calculated field of the sum of the size of table 1 divided by the staff of table 2, by color.

 

Thanks for all the help so far!

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

bwanRPD Im using your data and have this result

 

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

  • bwanRPD's avatar
    bwanRPD
    Frequent Visitor
    6 years ago

    i do see correct calculations in this, but for example, for the green color, it's missing values for 'square' when there should be values. i think it's happening to other data i'm applying this measure to as well, otherwise, it does seem to do the job. Do you know what the issue might be?

     

    az38