topic Re: Multiply rows within same table in DAX Commands and Tips

Multiply rows within same table

Anonymous — Wed, 14 Sep 2022 18:48:59 GMT

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

Re: Multiply rows within same table

Anonymous — Wed, 14 Sep 2022 20:27:06 GMT

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?

Re: Multiply rows within same table

Anonymous — Thu, 15 Sep 2022 00:40:00 GMT

Do you want to sum Result column?

Re: Multiply rows within same table

Anonymous — Thu, 15 Sep 2022 01:52:02 GMT

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

Re: Multiply rows within same table

Anonymous — Thu, 15 Sep 2022 02:05:11 GMT

where are the months. i only can see december

Re: Multiply rows within same table

Anonymous — Thu, 15 Sep 2022 02:06:59 GMT

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

Re: Multiply rows within same table

Anonymous — Thu, 15 Sep 2022 02:28:32 GMT

That was just a snapshot, here are

4 months.

Re: Multiply rows within same table

v-zhangti — Thu, 15 Sep 2022 06:01:10 GMT

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.

Re: Multiply rows within same table

Anonymous — Thu, 15 Sep 2022 06:22:46 GMT

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

Re: Multiply rows within same table

v-zhangti — Thu, 15 Sep 2022 07:02:32 GMT

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.

Re: Multiply rows within same table

Anonymous — Thu, 15 Sep 2022 07:49:32 GMT

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