5 Replies Latest reply on Mar 11, 2015 7:15 AM by Jonathan Drummey

    Sum_DISTINCT functionality works at my lowest data level, but not when I try to roll it up by Employee (caution* this is a long/tough one)

    Sam Brown

      All,

       

      Thanks ahead of time to anyone even reading this post and especially to those who try and lend a hand here.  If you don't have more than 5 mins, feel free to skip this one.

       

      I have transactional data that is being tested against company policies (tests).  If the transaction fails a test, the transaction data with the Policy ID that it has failed is copied to my failed table.

       

      So here is an example:

       

      ss1.png

      (screen shot 1)

       

       

      As shown, if a single transaction failed multiple tests, it is listed multiple times.  Probably not the best table format, I know, but we are really into the weeds now with the way its built and to move it "horizontal" would be a very large pain.

       

      I'm able to use a table calculation to get a sum of the AMOUNT (the measure in the screen shot above) by removing the Policy ID (remember that the tests that the transactions are failing) and creating a calculated field called "Distinct Sum" which as the formula:

       

      ss2.png

      (screen shot 2)

       

      Perfect! I get the results I need:

       

      ss3.png

      (screen shot 3)

       

      Now here's where I'm lost.  If I remove Amount USD from the ROWS (which its currently sitting there as a dimension) I get some funny results.  I've added the INDEX() as the last measure column below:

       

      ss4.png

      (screen shot 4)

       

      Why would the index start back at 1 for Transaction ID 3?  Both the Index and Sum Distinct are set to Computer Using = Transaction ID.

       

      The end goal here is to create a new sheet that will "roll-up" the distinct sum amount calculated in screen shot 3 by employee id and another sheet to roll-up the distinct count by Manager level. 

       

      I feel like I'm close, any additional insight would be very much appreciated.

       

       

      Attached is my Sum Distinct Test.twbx file.

       

      Thanks again.

       

      --

      Sam