Forum Discussion

oliviar's avatar
oliviar
Data Storage
08-08-2022

Apply a Formula to Select Rows in a Pivot Table

I have a pivot table and I'm pretty happy with how it looks. However, I want to adjust the formula in two of the columns, if possible.

The pivot table looks like this:

There is a formula in 2 of the value columns: LTD Remaining to Goal, and Forecasted Remaining to Goal.

I will only focus on one formula since I think the solution I'm searching for will be easily applied to both fields.

The formula for the LTD Remaining to Goal field = sum(REVENUE) - 110,000
110,000 is the goal

This formula applies to every Product Code in the Pivot Table.

I want to see all the codes shown in the Pivot Table, but I only want the LTD Remaining to Goal formula to calculate for Product Codes that start with BK.

I do not want the formula to calculate for any Product Code that starts with AB or ends in W.

Is this possible? How would you do this?

dashboards & reporting
Finance Analytics

1 Reply

Replies have been turned off for this discussion
  • HamzaJ's avatar
    HamzaJ
    Data Integration
    08-09-2022

    Hi oliviar 

    You could try a measured formula in combination with a case/if-statement. It is not possible to do a formula on string-values, so we  need to find another way

    Click on Title and select Filter. Configure on which values/strings you want to calculate LTD

    case
when (sum(revenue),Title)>0 then sum(revenue)-111000
else 0
end

    The formula calculates the revenue per title and if it contains your selection it will calculate the revenue, otherwise it will be zero. If the revenue is bigger then 0, it will calculate your LTD otherwise it will display 0 (replace 0 with another value or formula if needed)

    Hamza