3 Replies Latest reply on Oct 31, 2018 4:59 AM by Ombir Rathee

    Mix Fixed and Exclude

    Maxime Huot

      Hi guys,


      I was wondering if I can mix fixed values and exclude values within the same calculated field.


      At the moment i have fixed all my values:


      {FIXED [SubMarket], [Adress], [Region] , [Period] : SUM([Values])} /

      {FIXED [SubMarket], [Period] : SUM([Values)}




      The hierarchy under submarket is as follows:







      What i want is to exclude [Product] and keep  [Adress], [Region] , [Period] fixed



      {(Exclude [Product]) AND (Fixed [Adress], [Region] , [Period]) : SUM([Values])} /

      {(Exclude [Product]) AND (Fixed [Period]) : SUM([Values)}


      Thank you guys in advance.

        • 1. Re: Mix Fixed and Exclude
          Zhouyi Zhang

          Hi, Maxime


          Do you have a sample workbook to share and what's your expected result?



          • 2. Re: Mix Fixed and Exclude
            Jim Dehner

            good morning -

            yes you can but the syntax would be to nest the 2 lods like -

                 {exclude [product] : sum(  (Fixed [Adress], [Region] , [Period]) : SUM([Values])} )}

            and similar for the denominator


            since we can't see the workbook can't tell if this will produce the answer you want

            Please include your twbx workbook so the answer can be more specific to your need



            If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

            1 of 1 people found this helpful
            • 3. Re: Mix Fixed and Exclude
              Ombir Rathee

              Try this :


              {Fixed [Address],[Region],[Period]: SUM({EXCLUDE [Product]: SUM([Values])})}/

              {FIXED [Period] : SUM({EXCLUDE [Product]: SUM([Values])})}

              1 of 1 people found this helpful