6 Replies Latest reply: Jul 26, 2018 9:04 PM by Sunny Talwar RSS

    Sum, Group By and then Count

    Robin Sarkar

      I have a raw data table below which has multiple lines of same orders and revenue associated to it. I first want to Sum all the revenue based on the Orders and then want to count all the orders that are above says $500 I came up with below expression but doest seem to be working

       

      COUNT ({<Aggr(SUM(Revenue,[Order No])={">500"}>}[Order No])

       

       

      Below is the Example 

      Order No

      Revenue

      3703

      $     500.00

      3703

      $       50.00

      3703

      $       56.00

      3703

      $       20.00

      3805

      $     460.00

      3805

      $ 8,000.00

      3805

      $       23.00

      3805

      $     210.00

      3806

      $     499.00

      3811

      $       20.00

      3811

      $     100.00

      3811

      $     213.00

       

      First I need to sum up all the Revenue by their Orders

      This will Eventually look like this

       

      Order No

      Revenue

      3703

      $     626.00

      3805

      $ 8,693.00

      3806

      $     499.00

      3811

      $     333.00

       

      And the The orders Above $500 = 2

       

      How do I get these 2 steps in one expression? I tried the one above but doesn’t work.