INVALID FORMULA Error when using CASE with Aggregation

Hi All

I have data like below

Item         Quarter         TotalCost

Item1       FY19 Q4       100

Item1       FY20 Q1        120

The Total Cost Column is a Formula column from Business Schema and has some further calculations behind it.


For my Dashboard I need to use conditional logic and I am using CASE.

CASE(Quarter = "FY20 Q1", TotalCost,0) 

If I directly use this CASE as a New Formula and then select Aggregation from Setting while creating Insight, I get an error value as INVALID FORMULA as a result of the above CASE statement.

If I am not aggregating then I don't get this error.

Also if wrap the above CASE inside SUM/MAX like

SUM(CASE(Quarter = "FY20 Q1", TotalCost,0) )

and then select aggregation, I don't get the INVALID FORMULA value. But I dont want to wrap inside aggregation function.

Is there a way to resolve this error? 

I have seen one post regarding this error on Community, which says that this feature is not supported in current versions. So I wanted to know if there is any work around or solution to this INVALID FORMULA Error.


Also I don't want to display Quarter in my final results, so Level Based Measures using groupBY is also not an option I believe as I would need to select Quarter as Dimension field in Insight if I put it in groupBY.


Thank you.

3replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Ashwin Warrier Anurag Malik Amit Kothari  Could you please let me know if there is any workaround for this issue. In one other post I saw that the feature is not supported in the current version of Incorta (the updated on that post was 7 months back I believe)

    Reply Like
  • Yogesh, Can you share formula for "Total Cost".

    It is not recommended to use formula inside case statement. In certain scenario it will not work or return incorrect data.

    Reply Like
  • Anurag,

    Please see formula details below. There is quite a lot derivation behind.


    First Total Cost


    Each of these Fields are further having below derivation:

    -> MATERIAL_COST =  





    Similarly we have derivations for BPA_COST and PO_COST.

    Reply Like
Like Follow
  • 1 mth agoLast active
  • 3Replies
  • 32Views
  • 2 Following