Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
))
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]))
Many thanks! That works! 🙂