4 Replies Latest reply: Sep 28, 2016 8:03 AM by Michael Solomovich RSS

    Measure - Summing the Amount for Field based on distinct criteria for the other field in table

    Monica Chhabra

      Hi All,

      I have data that is pulled into one table with QuoteID and Amount field.  I am writing an expression for measure where I want to divide the Amount by the number of counts in QuoteID and then sum all the Amounts in the table.  Below is what I put in the syntax but seems like there is error in expression.  The data has QuoteID and amount associated to each QuoteID.  QuoteID is unique to most records but some QuoteID occur more than once and hence the Amount is multiplied by the number of times QuoteID apprears hence I want to divide the Amount by the Distinct(QuoteID) and then sum all the Amounts in the table in visualization.  Can someone please help?

       

      Regards,

      Monica

       

      Sum([Amount]/Count(Distinct([Quote ID]))