Forum Discussion
Using RELATED based on multiple columns
Hi, I'm trying to calculate a number bases on different percentages per category. I just can't figure out how to do this properly. Let me explain via an example. There is certain Amount per G...
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
mahoneypat
Microsoft Employee
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
Thanks for your response.
Table1 contains multiple values for Group and Amount for different area codes, there is no Brand in this table.
Table2 contains Group, Brand and Percentage.