Need help with making calculated field dynamic.

[Anonymous]

Hi everyone,

In the below data set, I want to make the columns "BU Country CP ETR2" and "Updated Line Item" to be more dynamic so I can show it in Power BI at either the consolidated level or at the country level. Right now, I did a calculated column at the BU country level and the formula works fine to show the percentage for "BU Country CP ETR2" in both the consolidated and BU country view, meaning that the percentage will be calculated correctly either at the aggregated level of both countries or separately between US and Ireland (the percentage is obtained by taking each row and divided by 'Pre-Tax . The issue am running into is the "Updated Line Item" is not aggregating correctly and I want it to be able to reflect the correct assignment of description at either the consolidated or country level. Right now, the logic is to assign anything in "BU Country CP ETR2" with absolute value greater than 0.02 (or 2%) as the description from "Line Item Description" and anything less would be 'Other' category. At the country level it assigns fine but when showing at consolidated level, it does not assign correctly, so for example, take 'Stock Compensation (Mp) (P03805-MP)' row from United States, right now it has the description from "Line Item Description" column in the "Updated Line Item" column because it has absolute value of 2.78% (which is greater than 2%). However, when showing at consolidated level, the percentage will be less because the sum of this row (of which there is only one) divided by the sum of rows 'Pre-tax Book Income (Loss)" would make the percentage less than 2%, however the "Updated Line Item" description is not updated to reflect this row at consolidated level as 'Other' category. How can I make it dynamically updated?

[Cmcmahan]

Calculated columns will only update when you refresh your entire dataset.  The fields won't dynamically update with whatever filters you're currently applying.

To get a result that dynamically updates, you need to create it as a measure instead of a calculated field.

[Anonymous]

Thank you so much for your reply. I had considered using a measure instead of a column but the issue am running into creating a measure is that because there are duplicate string in the "Line Item Description", i.e. 'Pre-Tax Book Income (Loss)', I run into the error below.

Am I missing something? How should I approach the formula?

[Anonymous]

Bumping up this thread in hope of someone has a solution to this issue.

[Cmcmahan]

There are a few ways to handle the error you're seeing.

The easiest is to use an aggregation.  If the context you plan to need the result in is only ever one item, you can use SELECTEDVALUE([Line Item Description]) in order to aggregate. 

The underlying problem is not because of having duplicates in your category column, but the measure not knowing which [Line Item Description] you mean.  When you're creating a calculated column, the context is inferred to be "current row".  You can read more about it here.