Calculation sumX multiplication

[Anonymous]

Hello;

i'm performing my calculation with the sumX function, in a simplified way, my code has the following form:

measure=sumx(A)*sumx(B)*sumx(C)

i'm getting the results as ( A1 + A2 + A3 ...)*(B1 + B2 + B3 ...)*(C1 + C2 + C3 ...)

What should i do in order to have my calculation performed in the following form: 
(A1*B1*C1) + (A2*B2*C2) + (A3*B3*C3) + ... ?

[tamerj1 · Accepted solution]

Hi @Anonymous 
Here is a sample file with the solution https://www.dropbox.com/t/obELBigMGJIPqhHd

Actually I have tried the same solutions and they seem to work. The thing is it won't allow me to create the hierarchy slicer with pop-up a message saying that the relationship should be One - Many. I don't know how did you create it but arbitrarily shaped slicers usually causes trouble. Using them would require the use of KEEPFILTERS. Unfortunately I failed to simulated your case but you can refer to (2) When to use KEEPFILTERS over iterators - YouTube

Following two workable solutions but using normal slicers.

[Anonymous]

Hello, Thank you tamer for your help i really appreciate it!
it worked with the KEEPFILTERS function!!

[tamerj1]

Hi @Anonymous 
are they in the same table or different tables?

[Anonymous]

actually 2 of the values are in the same table; and if i use this  

this pattern = SUMX( Table, Table[ColA] * Table[ColB])

for the 2 values in the same table i get the right value (A1*B1)+(A2*B2)...
but it's not working when i try to add the 3rd Value (C) which is in another table

[Anonymous]

different tables

[tamerj1]

@Anonymous 
Do they have 1:1 relationships? I mean how do you know which row to multply with which row? As you mentioned (A1*B1*C1) + (A2*B2*C2) + (A3*B3*C3) + ...   or do you mean (A1*B1*C1) + (A1*B1*C2) + (A1*B1*C3) + ... ?

[Anonymous]

i'm using a many to many relationship (power bi is not accepting to put it as 1:1); and using a hierarchy slicer, i'm chosing which value C corresponds to the 1st values (A1 & B1) ...

[tamerj1]

@Anonymous 
Still not 100% clear but you may try

Measure1 =
SUMX ( Table1, SUMX ( Table2, Table1[A] * Table1[B] * Table2[C] ) )

[Anonymous]

no still doesn't work, this formula gives me:
(C1+C2)*A1*B1 + (C1+C2)*A2*B2 (if i'm working with 2 values)

[tamerj1]

@Anonymous 
Ok. Then please try

Measure1 =
SUMX ( Table1, Table1[A] * Table1[B] * SUM ( Table2[C] ) )

[Anonymous]

it gives the same value as the previous one

[tamerj1]

@Anonymous 

Measure1 =
SUMX ( Table1, Table1[A] * Table1[B] * CALCULATE ( SUM ( Table2[C] ) ) )

[Anonymous]

same value as the previous one too 😭

[tamerj1]

@Anonymous 

Yes because there are two values related to each row. I need to have a look at the data structure. Can we connect via zoom or teams? Please share a link in a private message 

[Anonymous]

Hello Again,

Sorry i didn't get back to you but the file is confidential. i prepared the tables below to better show the problem.

But as seen in the photo above, i'm getting good results for each row alone. but for the total (Sum of the rows) the calculation is being perfomed in another way ( it's not simply making the sum of the calculation rows but it's it's making the sum of each column and then multiplying the results if using the measure sumx(A)*SumX(B)*sumx(C))
or

if using the measure sumx(A)*sumx(B*C), it's giving a correct result for each row alone too but for the total it's giving a value of the sum of all the values of column A multiplied by the sum of the multiplication of each row in column B and C

Note: column A is in a different table than columns B and C
i would really appreciate your help in solving this issue

[tamerj1]

Hi @Anonymous 
Please try

SUMX ( 
VALUES ( Nom ),
CALCULATE ( SUMX ( A ) * SUMX ( B * C ) )
)

[Anonymous]

it gives the same value as this one sumx(A)*sumx(B*C)
To explain more on the relationships i have:
i have 2 tables (one as database (containes the type, properties, B, C) and the other as entered data (containes Type, Nom, A))
the relationship is between Type and Type in both tables, and it's a double relationship relating many-to-many.

So on power Bi I will choose to associate BB to Yellow and GB2 to blue (as an example, or i can associate to any other properties(color)) and the calculation should perform the multiplication of the values of BB in column A with the corresponding values of Yellow in columns B and C

[CNENFRNL]

Follow this pattern = SUMX( Table, Table[ColA] * Table[ColB] * Table[ColC] )

[Anonymous]

but the values of A, B and C are from different tables.
(i'm using a hierarchy slicer in order to choose which value corresponds to which one)