Forum Discussion

pisca's avatar
pisca
Frequent Visitor
2 years ago
Solved

How To Calculate Total Matrix Using Average

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 res...
  • tamerj1's avatar
    tamerj1
    2 years ago

    Hi pisca 
    You may try

    Total Weight =
SUMX (
    SUMMARIZE ( 'Table', 'Table'[product_name], 'Table'[invoice-number] ),
    [Quantity (Pack)] * [Weigh (kg)]
)
  • pisca's avatar
    pisca
    2 years ago

    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's avatar
tamerj1
Community Champion
2 years ago

Hi pisca 
You may try

Total Weight =
SUMX (
    SUMMARIZE ( 'Table', 'Table'[product_name], 'Table'[invoice-number] ),
    [Quantity (Pack)] * [Weigh (kg)]
)
pisca's avatar
pisca
Frequent Visitor
2 years ago

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.