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])}



      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,