3 Replies Latest reply on Jun 29, 2018 11:33 AM by Deepak Rai

    Percent of Whole with Filters

    Scott Schmeling

      Good Afternoon;



      I have a WB where I am trying to Find Percentages but then have them adjust based on a filter for location.  Here is what I have so far:


      Internal Referrals =

      If [Category] = "Internal Referral" Then [ID]

      Else Null



      External Referrals =

      If [Category] = "Internal Referral" Then [ID]

      Else Null



      I have tried the following:

      {Fixed (DAtePart('month',[Date])), DAtepart('year',[Date]),[Referred from Location]: CountD([Internal Referrals])}/{Fixed(DAtepart("month",[Date])),DatePart("year",[Date]),[Referred from Location]: (CountD([Internal Referrals]) + CountD([External Referrals]))} :

           Won't work at all as there are nulls in the data.


      CountD(Internal Referral)/ CountD([External Referral]):

           Works until I add other categories to the table- then it gives me a solid 1


      I'm sure there is some LOD formula I need to do- however I just can't seem to find it.  Any suggestions would be appreciated.


      Please don't ask for WB as I can't due to HIPAA information.