SUMX calculation is not working for only one scenario

[hidenseek9]

Hi 

I am using the same calculation but this one calculation is not working. 

Can anyone figure out why that is?

I have a table with different volume scenarios as below. 

I am using NSKG(price) x volume to calculate sales such as below. 

All the calculation is identical, but for linear NS it is not working. 

Appreciate the support!

[v-pnaroju-msft]

Thankyou, @Shravan133, @danextian, for your response.

Hi @hidenseek9,

We appreciate your query on the Microsoft Fabric Community Forum.

Based on my understanding, since the measure Linear NS is not functioning while the measures Demand NS and Sales NS are operational, the issue may likely stem from the behavior of [Linear Vol] in the current model.

I would suggest the following approach to ensure proper row context, assuming [Linear Vol] is a measure. Please check if this resolves the issue:

Linear NS =

SUMX (

    ADDCOLUMNS (

        NSKG,

        "LinearVol", [Linear Vol]

    ),

    [LinearVol] * [RF NSKG] / 1000

)
 

This approach allows SUMX to evaluate [Linear Vol] correctly on a row-by-row basis. Additionally, please ensure that [Linear Vol] exists and is populated in the NSKG table and that there are no BLANK values or errors present in the data.

If you find our response helpful, kindly mark it as the accepted solution and provide feedback in the form of kudos. This will assist other community members who might be facing similar queries.

Thank you.

[danextian]

Hi @hidenseek9 

Your SUMX measure iterates over each row in the NSKG report, so the multiplication and division operations are performed row by row, rather than on the distinct values of the columns referenced in the black box. Use a table expression instead of a whole table.

Demand NS =
SUMX (
    ADDCOLUMNS (
        SUMMARIZE ( NSKG, NSKG[column1], NSKG[column2] ),  -- <-- Use your actual grouping columns
        "Vol", [Demand Vol],
        "RF", [RF NSKG]
    ),
    [Vol] * [RF] / 1000
)


Demand NS =
SUMX (
    VALUES(NSKG[column1]),
    [Demand Vol] * [RF NSKG] / 1000
)

[hidenseek9]

@danextian Thank you for the reply. This is highly complex.

Your solution worked at individual row but not for total as per below. 

Any reason why this may be?

[Shravan133 · Accepted solution]

Ensure that the table passed to SUMX accurately represents the data you intend to iterate over. Using functions like VALUES() or SUMMARIZE() can help define the correct context.​

For example: 

TotalValue = SUMX( VALUES('YourTable'[YourColumn]), [MeasureA] * [MeasureB] / 1000 )

[hidenseek9]

@Shhshsh Thank you for the solution. This worked perfectly!

Can you tell me what VALUES does specifically in this case?

I assume VALUES returns a distinct table, but without it what is SUMX referring to?

[Ashish_Mathur]

Hi,

The VALUES() function returns a table with unique entries.  Think of this as the UNIQUE() function of MS Excel.  The SUMX() function iterates over every row of this table and adds the measure value of each row.