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

[bwanRPD]

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

[Anonymous · Accepted solution]

@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

[az38]

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]

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]

@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

[bwanRPD]

that seemed to have worked! but then now i get this error. sorry, i'm a beginner in terms of Power BI formulas

A function 'SELECTEDVALUE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

[az38]

ok @bwanRPD 

try

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

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

[bwanRPD]

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]

@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]

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