How To Calculate Total Matrix Using Average

[pisca]

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?

[tamerj1 · Accepted solution]

Hi @pisca 
You may try

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

[pisca · Accepted solution]

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 

[tamerj1 · Accepted solution]

Hi @pisca 
You may try

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

[pisca]

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.

[pisca]

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.