How to sum the values of a measure in DAX?

[Anonymous]

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,

[v-chenwuz-msft · Accepted solution]

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.

[Avantika-Thakur]

Hi @Anonymous ,

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

[Anonymous]

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.

[Avantika-Thakur]

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

Please accept the solution if this answers your query.

Thanks!

Avantika

[Anonymous]

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.

Thanks for any additional suggestion.

Kind regards,