topic Re: How To Calculate Total Matrix Using Average in DAX Commands and Tips

How To Calculate Total Matrix Using Average

pisca — Fri, 19 Jul 2024 08:59:31 GMT

Hi,

I have a problem when I want to calculate the total weight from the multiplication of quantity and weight.

When I try to use several functions calculated, sum, summarize and average. The result is correct, but the total matrix is wrong.

My database is as below:

I want to produce as shown below:

What kind of dax function should be created so that the values are correct?

Re: How To Calculate Total Matrix Using Average

tamerj1 — Fri, 19 Jul 2024 10:07:07 GMT

Hi pisca
You may try

Total Weight = SUMX ( SUMMARIZE ( 'Table', 'Table'[product_name], 'Table'[invoice-number] ), [Quantity (Pack)] * [Weigh (kg)] )

Re: How To Calculate Total Matrix Using Average

pisca — Mon, 22 Jul 2024 01:31:12 GMT

Thanks tamerj1,

after I tried the dax above in the new column, the results are still not correct, because there is still the same product_name in another invoice_number.

totalweight = SUMX( SUMMARIZE( 'table', 'table'[product_name], 'table'[inv_number] ), 'table'[quantity] * 'table'[product_weight] )

build visual matrix as shown below

the result is like this.

I have tried changing the values sum to other options such as avarage, minimum, maximum, etc.
the results are still not as expected.

Re: How To Calculate Total Matrix Using Average

pisca — Mon, 22 Jul 2024 03:19:11 GMT

Hi tamerj1

Oh it worked when I made it as a measure.

So I create the same measure as total weight with the name quantity and weight, then I use the formula above, it works.

Give me time to try other product names. I'll let you know the results soon.

Re: How To Calculate Total Matrix Using Average

pisca — Mon, 22 Jul 2024 04:31:43 GMT

So, I create a measure quantity like this

quantity = SUMX( SUMMARIZE( 'table', 'table'[product_name], 'table'[inv_number] ), CALCULATE( AVERAGE( 'table'[quantity] ) ) )

and then, I made one more measure weight like this

weight = SUMX( SUMMARIZE( 'table', 'table'[product_name], 'table'[inv_number] ), CALCULATE( AVERAGE( 'table'[product_weight] ) ) )

last, I made one more measure total weight like this

totalweight = SUMX( SUMMARIZE( 'table', 'table'[product_name], 'table'[inv_number] ), [quantity] * [weight] )

the results were as I expected.

Thanks tamerj1