SUMX and CONCATENATEX give different results!

[nyarlathotep]

So, it took me hours to find this issue and I still cannot believe my eyes. 

Same calculation within the same measure with the same contexts, gives different results. 

Can someone explain please? 

This is the measure I use for debugging. You see everything is the same, only SUMX and CONCATEATEX differ.

WeightedScore =
CONCATENATEX( 
    VALUES( dimContacts[CID]),
    [Weight] * [Score per row]
) 
& " -- " & 
SUMX( 
    VALUES( dimContacts[cid]),
    [Weight] * [Score per row]
)

And this is the result I get, check the last column with the measure's output:

[mark_endicott · Accepted solution]

@nyarlathotep - in that case, just move the Weight calculation inside the final measure and you can use ALLSELECTED() 

VAR __allNetSales =
    CALCULATE ( [Net Sales] * ( [Score per row] <> 0 ), ALLSELECTED ( Data[CID] ) )
VAR __weight =
    DIVIDE ( [Net Sales], __allNetSales )
RETURN
    SUMX ( VALUES ( Data[cid] ), __weight * [Score per row] )

As for the riddle, I can only assume it's the way a string based Contcatenatex is dealing with context transition and the filter contexts.

[mark_endicott]

@nyarlathotep - did we resolve your issue? If we did, please select the appropriate solution, it helps with visibility for others and for SuperUsers to keep their status! Thanks!

[nyarlathotep]

@mark_endicott unfortunately no -- and to be clear, the issue was: why CONCATENATEX and SUMX are using different values for calculations under the same contexts.

The issue is also mentioned here, 2+ years ago:
https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Arbitrary-Shaped-Sets/m-p/2272097/hi...

Still no definite/official answer on this.

[pcoley · Accepted solution]

@nyarlathotep even thoug both measures initiates with the same context (because of the filter context applied at the report) there are context transitions that you don't seem to be taking into account. Please check the answers above, there are clear explanations of the logic created by powerbi to run your codes (it's not a bug, the code runs exactly as it should):
Any measure reference implies CALCULATE, which invokes the context transition.
In this case the measure with the sumx formula refers to 2 measures, and there will be a CALCULATE in each of them and due to the CALCULATE there will be the context transition within the iterator (SUMX). The "Weight" measure actually has its own CALCULATE; and the full code for the extended reference measure inside the SUMX will also be affected by the new CALCULATE and the outer filter context made by this new CALCULATE. That context transition is the reason why you have two different results. This is how power bi works.

[lbendlin]

As Marco had suggested in the other post this is likely due to different optimizations/query plans.  Maybe @jeffrey_wang has an opinion on that.

[pcoley]

I think this case is related to a context transition due to the new CALCULATE that results from the call of the [Weight] measure within the SUMX.
To avoid this problem, I suggest not calling the measure inside the iterator:
Sumx2 =
VAR __allNetSales = CALCULATE(
SUMX( VALUES( Data[CID] ), [Net Sales] * ([Score per row]<>0 )),
ALL SELECTED()
)
VAR __result = DIVIDE( [Net Sales], __allNetSales)
RETURN
SUMX(
VALUES(Data[cid]),
__result  * [Score per row]
)
I hope this solution ends your headache 😀 😀

[Anonymous]

Hi @nyarlathotep ,

I think the reason for the different results is that CONCATENATEX and SUMX have different functions:

CONCATENATEX: This function concatenates the results of evaluating the expression in each row of the table. It returns a text string that combines all the results.

SUMX: This function evaluates the expression in each row of the table and then sums the results.

In your measure, CONCATENATEX converts the results of [Weight] * [Score per row] to text and concatenates them, while SUMX sums those results to numbers.

As a result, CONCATENATEX converts numeric results to text, sometimes leading to unexpected formatting or rounding problems. Also, each function may run in a slightly different context, especially if implicit conversions or contextual conversions occur.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[nyarlathotep]

@Anonymous  thank you for replying, please check my additional info given to @lbendlin .

I am well aware of how SUMX and CONCATENATEX work, I used concat for debugging. Issue is that SUMX does not return the expected results -- also, running under the same contexts, I expected both concatx and sumx to return the same.

[lbendlin]

You are looking at the difference between sums of averages and averages of sums.

None of your initial activities (Score, Weight) require measures since your CIDs are unique.

[nyarlathotep]

@lbendlin thanks for checking this out -- but I think I cannot get my point across effectively.

Let's forget about my business logic and needs here, and just focus on the DAX used and the results returned.

So, I am using SUMX to sum the product of two values: [weight] * [score per row]. What i expect to see for each row in the table visual is just the product of the row's weight*score itself, since there is only one row to sum. In the totals row, it should sum the products of each row.

Since every row has the correct values shown for score & weight, why would their product in SUMX be wrong? Just consider these columns for example:

Here, all the rows show wrong products except for the total. So what is causing the product in the rows to be different for sumx vs. concatx? You can check the model in my othjer reply, here is the measure used for column "sumx"

Sumx = 
SUMX( 
    VALUES( Data[cid]),
    [Weight] * [Score per row]
)

 Thanks again for looking into this. It is driving me crazy, i cannot explain this.

[mark_endicott]

@nyarlathotep - Firstly, the fix is in your Weight measure - it's your use of ALLSELECTED( ) inside the calculate. Because you are just trying to calculate a total value, you dont need the CALCULATE at all. 

VAR __allNetSales =
    SUMX ( ALL ( Data[CID] ), [Net Sales] * ( [Score per row] <> 0 ) )
VAR __result =
    DIVIDE ( [Net Sales], __allNetSales )
RETURN
    __result

I'm not going to try to explain exactly what's happening when there are two Italian folk that can do it way more eloquently - https://www.sqlbi.com/articles/the-definitive-guide-to-allselected/ - scroll to the ALLSELECTED with no parameters section towards the bottom!

[nyarlathotep]

Hey @mark_endicott thanks for your reply, actually this measure is part of a larger calculation that goes over not ALL CIDs, but only those selected by the user -- CIDs are like customers, and they belong to groups, so report users might select only some of the groups, thus a subset of CIDs, not ALL.

But the actual riddle for me is not the business logic, forget that -- it is why CONCATX and SUMX behave differently, in exactly the same frame.

[mark_endicott · Accepted solution]

@nyarlathotep - in that case, just move the Weight calculation inside the final measure and you can use ALLSELECTED() 

VAR __allNetSales =
    CALCULATE ( [Net Sales] * ( [Score per row] <> 0 ), ALLSELECTED ( Data[CID] ) )
VAR __weight =
    DIVIDE ( [Net Sales], __allNetSales )
RETURN
    SUMX ( VALUES ( Data[cid] ), __weight * [Score per row] )

As for the riddle, I can only assume it's the way a string based Contcatenatex is dealing with context transition and the filter contexts.

[lbendlin]

Please provide more details. Are Weight and Score per row columns or measures?

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447... 

[nyarlathotep]

Sorry, i was a bit oveworked yesterday after so  much effort to debug this. Here is a .pbix reproducing the error (can't attach files here).

So I need to calculate the scores of CIDs weighted by net sales. I am using SUMX() because I need to insert this into a larger concept where there are groups of CIDs and I get the group's score (each CID contained in the group is weighted by net sales).

The obvious way to do this, a way that worked fine until recently is to iterate over CIDs, and sum each weighted score as in the measure "sumx". But this stopped working at some point! I guess some update messed with the functionality.

I used CONCATENATEX() while debugging the issue and was surprised to see that the "concatx" measure returned the expected results, ans "sumx" failed. 

Then I put both sumx & concatx in the same measure, to ensure that they were being calculated under the exact same contexts.

You can see the rest in the screenshot...