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 colu...
  • 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.

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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi, this is the result I expect. However, I would need to define up to 30 variables (as examples B and C in your example). Would there be a different way to do it? Variable A is ok and should always be used to multiply B, C, D, E...