Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Multiply rows within same table

Hi, 

 

How should a measure be written where each row in a table should be multiplied with a specific row from same table. In this simplified table B and C should be multiplied with A. And the column subtotals should add up correctly.

 

 Code1/20222/2022
 A1012
 B100100
 C200200
    
    
A x B =B10001200
A x C =C20002400
  • v-zhangti's avatar
    v-zhangti
    3 years ago

    Hi, Anonymous 

     

    Measure: 

    Total = IF(HASONEVALUE('Table'[Date]),[Measure],SUMX('Table',[Measure]))

    Result:

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

10 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    I managed to get the multiplying to work, now I am just struggeling with the column subtotals, I need the subtotal to add up all the results,  not price x volume . This seems to be a commin problem with DAX?

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Do you want to sum Result column?

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Yes, I want to sum the results from the all individual months, I do not want the column subtotal to be calculated as [price] x [volume].

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      How did you format your table like that. that combinated headers?

  • v-zhangti's avatar
    v-zhangti
    Community Support
    3 years ago

    Hi, Anonymous 

     

    This function controls the output of Total.
    IF    (HASONEVALUE(Columns), Value, Total).

    Sample data:

    Measure:

    Measure = 
Var _A=CALCULATE(SUM('Table'[Price]),FILTER(ALL('Table'),[Code]="A"&&[Date]=SELECTEDVALUE('Table'[Date])))
Var _B=CALCULATE(SUM('Table'[Price]),FILTER(ALL('Table'),[Code]="B"&&[Date]=SELECTEDVALUE('Table'[Date])))
Var _C=CALCULATE(SUM('Table'[Price]),FILTER(ALL('Table'),[Code]="C"&&[Date]=SELECTEDVALUE('Table'[Date])))
Return
IF(SELECTEDVALUE('Table'[Code])="A",BLANK(),IF(SELECTEDVALUE('Table'[Code])="B",_A*_B,_A*_C))

    Result:

    Total = IF(HASONEVALUE('Table'[Date]),[Measure],SUM('Table'[Price]))

    Result:

    Hope this function is applied to help you.

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi, 

       

      Thanks for your input, maybe with some modification your solution might work. In the result matrix I would need these sums:

      B 1000+1200 = 1200
      C 2000+2400 = 4400

      • v-zhangti's avatar
        v-zhangti
        Community Support
        3 years ago

        Hi, Anonymous 

         

        Measure: 

        Total = IF(HASONEVALUE('Table'[Date]),[Measure],SUMX('Table',[Measure]))

        Result:

        Is this the result you expect?

         

        Best Regards,

        Community Support Team _Charlotte

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.