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 Group based on a current selection.
The Percentage is static and depends on both Group and Brand found in Table2 (which includes a column for Group, Brand and Percentage)
When we multiple Amount and Percentage we get Total.
Total = SUM('Table1'[Amount]) * SUM('Table2'[Percentage])
So far so good.
Now I would like to show the Total (%) in a pie chart for example.
Brand is put as Legend, Total is put as value.
Let's look at "Batavus" to explain my problem. It's giving the amount 677,5 (28,2%) because the measure Total states to sum the percentages. It takes (16,54% + 16,04%) * (518 + 1561) = 677,5.
What I actually would like to do is get the Total % from the table above (3,57% + 10,43%) = 14% instead of 28,2%. The Group context is missing. I thought maybe the solution is to use SUMX to first calculate the separate values per Group and then add them. The problem with this is that the relation from Table1 to Table2 is based on just the Group (these values are repeated in Table2) which is why i cannot use RELATED for this measure (it's missing which Brand to use).
Could anybody please explain how te setup my measure correctly?
Hopefully my explanation is clear enough. Thank you for your time and help.
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
8 Replies
If 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
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
hi Anonymous
In fact, the total for measure[Total] is 1203.7 not 2400.5
and for total measure, I would suggest you use this formula
Total = SUMX(Table2,[Percentage]*RELATED(Table1[Amount]))Regards,
Lin
Thank you for your responses. I feel like I haven't explained correctly what I'm trying to do.
These are simplified versions of the tables:
The relation is Group to Group via a third table with unique Groups-values (* <-> 1, 1 <-> *).
What happens now if I filter Area code "1" and Brand "Batavus":
(518+1561) * (0.1654+0.1604)
What I would like to calculate if I filter Area code "1" and Brand "Batavus" for example:
518 * 0.1654 + 1561 * 0.1604
This way I can show the total Amount * Percentage per Brand
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
