Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
pisca
Frequent Visitor

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:

pisca_1-1721379147177.png

I want to produce as shown below:

pisca_0-1721379057341.png

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

2 ACCEPTED SOLUTIONS
tamerj1
Super User
Super User

Hi @pisca 
You may try

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

View solution in original post

pisca
Frequent Visitor

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 

View solution in original post

4 REPLIES 4
pisca
Frequent Visitor

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
Super User
Super User

Hi @pisca 
You may try

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

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
Frequent Visitor

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

pisca_0-1721611244254.png

the result is like this.

pisca_1-1721611738094.png

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.