Forum Discussion
Using RELATED based on multiple columns
HI Anonymous
Ok, try this measure
Total 2= SUMX('Table 1',[Amount]*CALCULATE(SUM('Table 2'[Percentage])))Result:
and here is sample pbix file, please try it.
Regards,
Lin
Microsoft EmployeeIf you have both Group and Brand in both Table1 and Table2, I would concatenate those fields in a new column in both tables, and use that for your relationship. You can these use a SUMX for your measure like
New Total = SUMX(Table1, Table1[Amount] * RELATED(Table2[Percentage]))
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
It feels to me like the Percentage shouldn't be used with SUM but with some other operator, so the percentages won't be summed before multiplying with the amount. I just don't know how to do this.
Total = SUM('Table1'[Amount]) * SUM('Table2'[Percentage])
- mahoneypat
With an expression that uses RELATED(), you can make it so you use a single percentage value at a time (and not have to worry about SUM vs. AVERAGE, etc.). What is the relationship between Table1 and Table2 (which columns? 1:Many?, filter direction(s)?) Do your brands exist in more than one group as the table visual suggests?
Regards,
Pat
- amitchandak
Super User
Anonymous , You can move data from one table to another like
Item Name = RELATED('item'[Brand])
City Name = Maxx(FILTER(geography,geography[City Id]=Sales[City Id]),geography[City]) // you can use sumxFor multiplication measure, please force a common context
Total = Sumx(values(Dim[Dim1]),SUM('Table1'[Amount]) * SUM('Table2'[Percentage]))
Total = Sumx(summarize(Dim,Dim[Dim1],"_1",SUM('Table1'[Amount]) ,"_2" SUM('Table2'[Percentage])),[_1]*[_2])
Refer to the role of context
