can't get column total with sumx

[user9394]

Why is this not working? I'm trying to get  returnRate*big total.  the grand total is not adding up.

final = 

var bigtotal = [total_new_qty]/CALCULATE([total_new_qty], ALLSELECTED('dataset'[group]))
var returnRate  = ([total_cost]/[old_total_qty])
var prod = returnRate*bigtotal
return SUMX(VALUES('dataset'[group]), prod)

[v-jianboli-msft]

Hi @user9394 ,

This is caused by a very important concept in dax - context.
EARLIER is mainly used in the context of a computed column, it returns the current value of the specified column and evaluates the column externally.

For more details, please refer to:

EARLIER function (DAX) - DAX | Microsoft Learn

Context in DAX Formulas - Microsoft Support 

Best Regards,

Jianbo Li

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

[v-jianboli-msft · Accepted solution]

Hi @user9394 ,

Based on your description, I have created a simple sample:

Please try:

final =
VAR _a =
    SUMMARIZE (
        'dataset',
        'dataset'[group],
        "Rate",
            CALCULATE (
                SUM ( 'dataset'[cost] ),
                FILTER ( ALL ( 'dataset' ), [group] = EARLIER ( 'dataset'[group] ) )
            )
                / CALCULATE (
                    SUM ( 'dataset'[oldqty] ),
                    FILTER ( ALL ( 'dataset' ), [group] = EARLIER ( 'dataset'[group] ) )
                ),
        "BigTotal",
            CALCULATE (
                SUM ( 'dataset'[totaqty] ),
                FILTER ( ALL ( 'dataset' ), [group] = EARLIER ( 'dataset'[group] ) )
            )
                / CALCULATE ( SUM ( 'dataset'[totaqty] ), ALL ( 'dataset' ) )
    )
RETURN
    SUMX ( _a, [BigTotal] * [Rate] )

 Final output:

If this measure can't work, please provide more details about the other measures that you used and your source data after removing sensitive data.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Jianbo Li

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

[user9394]

It works!  Can you explain why it works? what is "earlier" and why are there two calculate functions for each virtual column that is created?

[amitchandak]

@user9394 , Try

final =

var bigtotal = [total_new_qty]/CALCULATE([total_new_qty], ALLSELECTED('dataset'[group]))
var returnRate = ([total_cost]/[old_total_qty])
var prod = returnRate*bigtotal
return SUMX(VALUES('dataset'[group]), calculate(prod))

Or Two measure approach

final =

var bigtotal = [total_new_qty]/CALCULATE([total_new_qty], ALLSELECTED('dataset'[group]))
var returnRate = ([total_cost]/[old_total_qty])
var prod = returnRate*bigtotal
return prod

final Sum = SUMX(VALUES('dataset'[group]),[final])

[user9394]

the first approach gives me the correct row toal, but not the grand total

The second approach gives me the grand total, but not the row total