3 Replies Latest reply on Aug 21, 2018 1:37 PM by Okechukwu Ossai

    FIXED then IF/EXCLUDE by dimension and measure

    Shan Sasser

      Hello! My first post. Hopefully, I've attached my sample packaged workbook correctly.

       

      I need to determine:

      • Total distinct client count and total units.
      • Total distinct client count and total units by service.

       

      Criteria:

      • Exclude from counts those clients with less than 0.50 total units for Service 1 .
      • Exclude from counts those clients with less than 0.50 total units for Service 2.
      • Include clients regardless of total units for Services 3 & 4.

       

      See client Y026 as an example of a client that should be included in total count & service 3 & 4 counts, but not service 2 counts. Client D079 should be excluded entirely.

       

      Current Result with no filters or excludes

      SampleTtlConsUnits.JPG

      Service 1 clients: 3 units: 4.75

      Service 2 clients: 5 units: 10.75

      Service 3 clients: 5 units: 50.35

      Service 4 clients: 4 units: 22.5

      Distinct Count: 13

      Total Units: 88.35

       

      Desired Result

      SampleTtlConsUnits2.JPG

       

      Service 1 clients: 2 units: 4.5

      Service 2 clients: 4 units: 10.5

      Service 3 clients: 5 units: 50.35

      Service 4 clients: 4 units: 22.5

      Distinct Count: 12

      Total Units: 87.85

       

      I created a simple exclude/include calculated field as a test for Service1.  This approach is not working because the units are not summed first. I believe I need to utilize LOD {FIXED [Client Id]: SUM([Units])} to obtain the total units for the client ID first. Then, filter or exclude based on the service option. I'm just not sure how to pull it all together. Thanks for your assistance!