Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Calculation sumX multiplication

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...
  • tamerj1's avatar
    tamerj1
    4 years ago

    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.

tamerj1's avatar
tamerj1
Community Champion
4 years ago

Anonymous 
Ok. Then please try

Measure1 =
SUMX ( Table1, Table1[A] * Table1[B] * SUM ( Table2[C] ) )
Anonymous's avatar
Anonymous
Not applicable
4 years ago

it gives the same value as the previous one

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Anonymous 

    Measure1 =
SUMX ( Table1, Table1[A] * Table1[B] * CALCULATE ( SUM ( Table2[C] ) ) )
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    same value as the previous one too 😭

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    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's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
    tamerj1
    Community Champion
    4 years ago

    Hi Anonymous 
    Please try

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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