Cascading subtotals RRS feed

  • Question

  • I've been reading this site http://www.powerpivotpro.com/2011/09/profit-lossthe-art-of-the-cascading-subtotal/ about cascading subtotals. 

    It seems good, except that all my data is positive and it looks like I need it be positive for the revenue and negative for the expenses. Is there a way I can change that without changing my root data? 

    Friday, November 4, 2016 9:41 PM


  • Hi AlexMartini,

    For the root data in the article, the sale value is negative and the cost is positive, so using the following formula to correct it oppositely.

    Value_Corrected = SUM(FACT_Tran[Value]) * -1

    For your issue, you also can use the similar formula like the formulas below.

    Create a calculated column, Value_Corrected0=IF([Name]=revenue,[Value],-1*[Value])

    Then create a measure, Value_Corrected = SUM(Table[Value])

    If you have any question, please let me know.

    Best Regards,

    Tuesday, November 8, 2016 7:52 AM