cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Anonymous
Not applicable

## How to sum the values of a measure in DAX?

Dear All,

I do have a simple matrix in Power BI, where the values are taken from a measure. See below screenshot:

I want to create the column 'Total' with a DAX function as measure, too.

Question: how can I create a new measure which is a sum of the values in the measure number of orders shipped? (ranging from Jan  to June in this example). Since this isn't column name / field, I cannot use for example the formula:

CALCULATE(SUM(table[number of orders shipped], ALL(table))

Any suggestion is welcome

Kind regards,

1 ACCEPTED SOLUTION
Community Support

Hi @Anonymous ,

You need to create virtual table via SUMMARIZE() in measure to do that.

``````Total =
VAR _s =
SUMMARIZE (
'Table',
[customer number],
[customer name],
date[month],
"v_Number of orders shipped", [number of orders shipped]
)
RETURN
SUMX ( _s, [v_Number of orders shipped] )
``````

Result:

If i missunderstood you, please let me know.

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 REPLIES 5
Community Support

Hi @Anonymous ,

You need to create virtual table via SUMMARIZE() in measure to do that.

``````Total =
VAR _s =
SUMMARIZE (
'Table',
[customer number],
[customer name],
date[month],
"v_Number of orders shipped", [number of orders shipped]
)
RETURN
SUMX ( _s, [v_Number of orders shipped] )
``````

Result:

If i missunderstood you, please let me know.

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Solution Supplier

Hi @Anonymous ,

You can try using CALCULATE( SUMX(Table,[number of orders shipped]), ALLEXCEPT(table,table'customer number'))

Anonymous
Not applicable

Thanks for this suggestion, but I am still struggling with the fact that Number of orders shipped  is not a field name in table, but just a measure. The SUM or SUMX function does not work.

Solution Supplier

Hi @Anonymous ,

Assuming that you need the total of Number of orders shipped for each customer , you can try using below formula -

CALCULATE(SUM(table[number of orders shipped], ALLEXCEPT(table,table'customer number'))

Thanks!

Avantika

Anonymous
Not applicable

Thanks for your feedback and correctly understood regarding the number of orders shipped by customer number is required.

There is a challenge on the field Number of order shipped. It's not a field name in a table. It's a measure and there I cannot execute the first part of the formula (CALCULATE(SUM(table[number of orders shipped].......)

I am seeking for an alternative.

Kind regards,