topic Calculation sumX multiplication in DAX Commands and Tips

Calculation sumX multiplication

Anonymous — Thu, 02 Jun 2022 08:26:39 GMT

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) + ... ?

Re: Calculation sumX multiplication

CNENFRNL — Thu, 02 Jun 2022 08:47:47 GMT

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

Re: Calculation sumX multiplication

Anonymous — Thu, 02 Jun 2022 08:53:16 GMT

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)

Re: Calculation sumX multiplication

tamerj1 — Thu, 02 Jun 2022 09:20:10 GMT

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

Re: Calculation sumX multiplication

Anonymous — Thu, 02 Jun 2022 09:23:04 GMT

different tables

Re: Calculation sumX multiplication

Anonymous — Thu, 02 Jun 2022 09:31:27 GMT

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

Re: Calculation sumX multiplication

tamerj1 — Thu, 02 Jun 2022 09:32:09 GMT

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) + ... ?

Re: Calculation sumX multiplication

Anonymous — Thu, 02 Jun 2022 09:38:02 GMT

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) ...

Re: Calculation sumX multiplication

tamerj1 — Thu, 02 Jun 2022 09:42:56 GMT

Anonymous
Still not 100% clear but you may try

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

Re: Calculation sumX multiplication

Anonymous — Thu, 02 Jun 2022 09:49:29 GMT

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

Re: Calculation sumX multiplication

tamerj1 — Thu, 02 Jun 2022 09:53:56 GMT

Anonymous
Ok. Then please try

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

Re: Calculation sumX multiplication

Anonymous — Thu, 02 Jun 2022 09:56:38 GMT

it gives the same value as the previous one

Re: Calculation sumX multiplication

tamerj1 — Thu, 02 Jun 2022 09:58:20 GMT

Anonymous

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

Re: Calculation sumX multiplication

Anonymous — Thu, 02 Jun 2022 10:00:15 GMT

same value as the previous one too 😭

Re: Calculation sumX multiplication

tamerj1 — Thu, 02 Jun 2022 10:03:47 GMT

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

Re: Calculation sumX multiplication

Anonymous — Wed, 08 Jun 2022 08:19:53 GMT

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

Re: Calculation sumX multiplication

tamerj1 — Wed, 08 Jun 2022 08:58:39 GMT

Hi Anonymous
Please try

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

Re: Calculation sumX multiplication

Anonymous — Wed, 08 Jun 2022 09:11:17 GMT

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

Re: Calculation sumX multiplication

tamerj1 — Thu, 09 Jun 2022 04:25:07 GMT

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.

Re: Calculation sumX multiplication

Anonymous — Thu, 09 Jun 2022 08:23:57 GMT

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