2 Replies Latest reply: Oct 17, 2017 10:17 AM by Lukas Schomberg RSS

    Total Sum in Tables - total sum ignoring the formula

    Lukas Schomberg

      Hey everyone,

       

      we need your help please:

      In the above table we are calculating some figures per branch (e.g. Actual Malus Delivery Quota). The problem is that the total line is always using the the formula as all other lines within the table. BUT we need to have a total sum (more or less ignoring the above mentioned formula). In this case our desired result would be 2.900 € instead of 900 €.

      A possible solution could also be that the figure is displayed in a different object (e.g. KPI, Second table etc.).

      But here comes our second problem:

      Our formula (see formula at the bottom) is based on Branch level therefore the it is a bit difficult to get our desired result on country level.

      If you need any additional information to help us - please contact us!!! We really have to solve this problem and would really appreciate if you could help us!

      Many, many thanks in advance!

       

      Best regards

       

      Lukas & Whiteside

       

      :-)

       

      if((if((round(0.90 - (count({<[QR6 Timestatus]={'in time'}>}[Shipment no])

      /

      (count([Shipment no]) - count({<[QR6 Timestatus]={'island shipment','ZIP-Code exists in platform, but not in branch','ZIP-Code in branch and platform missing'}>}[Shipment no])) ),0.01)*100*150)>0,(round(0.90 - (count({<[QR6 Timestatus]={'in time'}>}[Shipment no])

      /

      (count([Shipment no]) - count({<[QR6 Timestatus]={'island shipment','ZIP-Code exists in platform, but not in branch','ZIP-Code in branch and platform missing'}>}[Shipment no])) ),0.01)*100*150),0)

      )>(if((count([Shipment no])< 51), 100 , if((count([Shipment no]) < 101), 500 , if((count([Shipment no]) < 1501),1000 ,1500)))),(if((count([Shipment no])< 51), 100 , if((count([Shipment no]) < 101), 500 , if((count([Shipment no]) < 1501),1000 ,1500)))),(if((round(0.90 - (count({<[QR6 Timestatus]={'in time'}>}[Shipment no])

      /

      (count([Shipment no]) - count({<[QR6 Timestatus]={'island shipment','ZIP-Code exists in platform, but not in branch','ZIP-Code in branch and platform missing'}>}[Shipment no])) ),0.01)*100*150)>0,(round(0.90 - (count({<[QR6 Timestatus]={'in time'}>}[Shipment no])

      /

      (count([Shipment no]) - count({<[QR6 Timestatus]={'island shipment','ZIP-Code exists in platform, but not in branch','ZIP-Code in branch and platform missing'}>}[Shipment no])) ),0.01)*100*150),0)

      ))

        • Re: Total Sum in Tables - total sum ignoring the formula
          Sunny Talwar

          Wrap your expression around with Sum(Aggr()) like this

           

          Sum(Aggr(

          if((if((round(0.90 - (count({<[QR6 Timestatus]={'in time'}>}[Shipment no])

          /

          (count([Shipment no]) - count({<[QR6 Timestatus]={'island shipment','ZIP-Code exists in platform, but not in branch','ZIP-Code in branch and platform missing'}>}[Shipment no])) ),0.01)*100*150)>0,(round(0.90 - (count({<[QR6 Timestatus]={'in time'}>}[Shipment no])

          /

          (count([Shipment no]) - count({<[QR6 Timestatus]={'island shipment','ZIP-Code exists in platform, but not in branch','ZIP-Code in branch and platform missing'}>}[Shipment no])) ),0.01)*100*150),0)

          )>(if((count([Shipment no])< 51), 100 , if((count([Shipment no]) < 101), 500 , if((count([Shipment no]) < 1501),1000 ,1500)))),(if((count([Shipment no])< 51), 100 , if((count([Shipment no]) < 101), 500 , if((count([Shipment no]) < 1501),1000 ,1500)))),(if((round(0.90 - (count({<[QR6 Timestatus]={'in time'}>}[Shipment no])

          /

          (count([Shipment no]) - count({<[QR6 Timestatus]={'island shipment','ZIP-Code exists in platform, but not in branch','ZIP-Code in branch and platform missing'}>}[Shipment no])) ),0.01)*100*150)>0,(round(0.90 - (count({<[QR6 Timestatus]={'in time'}>}[Shipment no])

          /

          (count([Shipment no]) - count({<[QR6 Timestatus]={'island shipment','ZIP-Code exists in platform, but not in branch','ZIP-Code in branch and platform missing'}>}[Shipment no])) ),0.01)*100*150),0)

          ))

          , [Branch country], [Branch city]))