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

4 Replies

  • pisca's avatar
    pisca
    Frequent Visitor
    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

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