topic Filer Context of a calculated column in a pivot table in DAX Commands and Tips

Filer Context of a calculated column in a pivot table

Dicken — Sun, 23 Jun 2024 17:46:13 GMT

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.

Re: Filer Context of a calculated column in a pivot table

NaveenGandhi — Sun, 23 Jun 2024 18:07:12 GMT

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!!

Re: Filer Context of a calculated column in a pivot table

rajendraongole1 — Sun, 23 Jun 2024 18:08:53 GMT

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!!

Re: Filer Context of a calculated column in a pivot table

Dicken — Sun, 23 Jun 2024 18:13:07 GMT

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

Re: Filer Context of a calculated column in a pivot table

lbendlin — Sun, 23 Jun 2024 18:22:17 GMT

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

Re: Filer Context of a calculated column in a pivot table

Dicken — Sun, 23 Jun 2024 18:26:26 GMT

Hi,
Thanks, that is whats happening,
Richard.