5 Replies Latest reply on Nov 7, 2018 8:42 AM by Deepak Rai

    Excluding Certain Values from an LoD expression

    Eoghan Lyons

      Hi all,

       

      I've seen this asked before but the suggested action didn't work when I tried it.

       

      I have a few categories in a dashboard, and I'm looking to find the relative share within each of them.

       

      This works fine when everything is included.

       

      My basic formula is to have a "Total Records" formula as follows: {fixed  [Category]: sum([Number of Records])}

       

      I then apply a formula for the share to be: SUM([Number of Records])/SUM([Total Records]). Generally this works fine.

       

      However, a problem then arises when I want to apply a filter beyond this. For example, I'd like to know what the Total Records are if I exclude a certain member within a category from the result.

       

      I have tried an if statement on the Total Records formula of:

       

      if [Item]="variable to omit" then 0 ELSE

      {fixed  [Category]: sum([Number of Records])}

      END

       

      This had the undesired result of increasing the Total Records to a huge amount for some reason (as opposed to shrinking it).

       

      Can anyone give me any advice as to what I did wrong?

       

      Many thanks,

      Eoghan