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...
dashboards & reporting
Finance Analytics
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