topic Re: Total Sum in Tables - total sum ignoring the formula in App Development

Total Sum in Tables - total sum ignoring the formula

Anonymous — Tue, 17 Oct 2017 09:56:16 GMT

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)

))

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

sunny_talwar — Tue, 17 Oct 2017 12:31:32 GMT

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)

))

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

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

Anonymous — Tue, 17 Oct 2017 14:17:08 GMT

Many thanks! That works! 🙂