Filer Context of a calculated column in a pivot table

[Dicken]

  Hi,   I  have  a   Caluculated column  equivalent sumproduct ; 

 SUMX( Table1, Table1[Price]*Table1[Unit]) 

Fine, but when used in a pvot table I get back some very strange results,, so can someone explain or point to information 

on how these incorrect or at least unwanted results are  being calcuated, the actural result is 2267? 

please don't suggest a measure, that will not help me unserstand whats happening here 🙄

Richard. 

[Dicken]

Hi, 
Thanks, that is whats happening,
Richard. 

[lbendlin]

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[Dicken]

Hi, I cna't do this right now, I acutually deleted the example, but tomorrow I will replicate it and do some counting, 
that does  seeem like the reason, it's a bit lke the average aggreation problem when you have duplicate rows, 
will let you know, thank you for replying , 
Richard 

[rajendraongole1]

Hi @Dicken -As you know, calculated columns are computed row-by-row and are static, meaning they don't change based on filters in the pivot table.

where as this could lead to unexpected results if the underlying data model or relationships are not correctly configured. as we need to verifying data and model relationships should help identify the issue.

If you need dynamic calculations based on the pivot table context, you would generally use measures, but as you've requested, this explanation focuses on understanding calculated columns..

if you are using the calculated column: directly you can use the calculation as below , no need to add sumx function.

Calculated Column = Table1[Price] * Table1[Unit]

as you mentioned the above expected result 2267possible issue with data , as i mentioned above   basically calculated columns do not change based on the filters or slicers applied in a pivot table. This means they are static once calculated, which might not reflect the desired behavior when interacting with other data.

while doing the pivot table is correctly aggregating the calculated column. 

Hope the above expaination is clear . 

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[NaveenGandhi · Accepted solution]

Hi @Dicken 

from what i understand, the pivot table is doing sum of the calculated column 1, Look at the below table and can you confirm that the  occurence of item in original table is equal to column D?  

Let me know if this helps or feel free to share the sample file to have a look at.

If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate you kudos!!