Multiply rows within same table

[Anonymous]

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.

	Code	1/2022	2/2022
	A	10	12
	B	100	100
	C	200	200
A x B =	B	1000	1200
A x C =	C	2000	2400

[v-zhangti]

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]

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 · Accepted solution]

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]

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

[Anonymous]

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]

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

[Anonymous]

Do you want to sum Result column?

[Anonymous]

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]

where are the months. i only can see december

[Anonymous]

That was just a snapshot, here are

4 months.