I am building a sheet that displays two tables. One shows a detailed view, and the other I want to display one recommended row per dimension based on a criteria entered by the user.
Detail Table columns:
-Origin/Destination Pairs
-Transit Time in Days
>calculated as: =aggr(only({<[Delivered shipment count] = {'1'}> * <[InTransit Time] = {">0"}>} [InTransit Time]), ODPairTransit)
as my Dimensions
-Cumulative OnTime %
>calculated as: =(rangesum(above(sum({<[Delivered shipment count] = {'1'}>} [Delivered shipment count]), 0, RowNo()))
/Aggr(NODISTINCT sum({<[Delivered shipment count] = {'1'}> } [Delivered shipment count]), ODPair))
as my measure
I want my Recommended table to show one Transit Time per each OD Pair where the Cumulative % is over a certain value entered by the user and stored in a variable. ex. 0.85 (for 85%).
I have tried to use the limitation >= my variable, but this doesn't work. I created a work around in the load editor, but this generates a static table. I need the user to be able to make selections based on Year, Carrier, etc.
ODPairTransit is just a concat of ODPair and the Transit Time generated in the load script.
The purpose of this is to show the recommended Transit Time required to meet or exceed the desired On Time % (ex. 85%).
