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



      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:



      (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:



      (screen shot 2)


      Perfect! I get the results I need:



      (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:



      (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.