Using RELATED based on multiple columns

[Anonymous]

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.

[Anonymous]

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

[v-lili6-msft · Accepted solution]

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

[v-lili6-msft]

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

[mahoneypat]

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

[Anonymous]

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]

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

For 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

https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Difference-Across/ba-p/934397#M451

[Anonymous]

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.