getting the values which are in top10

[G3S]

hi

There is a pivot which has a single dimension 'location' in columns, and the values are in the rows section. 

the values are : 

1. master measure: allocation to the carrier . (limited to top10 through the data section in the pivot) 

sum(aggr(sum(distinct Allocation),Key1_Carrier))

2. master measure: performance per carrier. 

sum(aggr(ShippedTEU,CntrSeal))/sum(aggr(sum(distinct Allocation),Key1_Carrier))

3. what is required:   when user  makes selections in 1 or more filters and a carrier is also selected. considering the filters excluding the carrier selection,  the performance % of all carriers for the top10 locations shown through master measure #1.

for example carrier 1 may show top10 as: location 1,2,3..10.

carrier 2 might be location 1,2,7,9,10. 

need to calculate performance for all carriers for these locations that get shown in the pivot. 

thanks in advance.

[Chanty4u]

Hi try this

sum(

    aggr(

        if(GetSelectedCount(Carrier) = 0 or WildMatch(Carrier, GetCurrentSelections(Carrier, ', ')) > 0,

            ShippedTEU / sum({1<Location = P({1<Location = {"=Rank(sum({<Carrier=>}Allocation), 1, 1))<=10"}>} Location)}>0

        ),

        Carrier, Location

    )

) / sum(distinct Allocation)

[G3S]

hi @Chanty4u 

Thank you. expression returned 0%. expected value for the selected period is 51% for  location1

I added a missing closing parenthesis (4th row from bottom in your code). using the dimension in the dataset it looks like this 

sum(

aggr(

if(GetSelectedCount(Carrier_Filter) = 0 or WildMatch(Carrier_Filter, GetCurrentSelections(Carrier_Filter, ', ')) > 0,

ShippedTEU / sum({1<POL_Filter = P({1<POL_Filter = {"=Rank(sum({<Carrier_Filter=>}Allocation), 1, 1))<=10"}>} POL_Filter)}>0

)

),
Carrier_Filter, POL_Filter )

) / sum(distinct Allocation)

[G3S]

I think the shippedTEU has to have the set expression to : 1. ignore carrier selection 2. get the total shippedTEU for the top10 location by allocation for the selected carrier. 

is anyone able to please help with the set analysis with that?

currently to measure total performance regardless of carrier, this expression is used. the location part needs to be added to this. I am not clear on how to add both requirements

sum({<Carrier_Filter=>}aggr(ShippedTEU,CntrSeal))

[vinieme12]

please post some sample data or app

[G3S]

pls see below:

Carrier_Filter	POL_Filter	ShippedTEU	Allocation
Carrier 1	GBLGP	0	102
Carrier 1	BEANR	10	46
Carrier 1	NLRTM	5	26
Carrier 1	ITSPE	4	18
Carrier 1	DEHAM	3	14
Carrier 1	FRLEH	1	12
Carrier 1	GBFXT	0	10
Carrier 1	ITNAP	0	10
Carrier 2	BEANR	58	65
Carrier 2	ITNAP	65	51
Carrier 2	NLRTM	10	22
Carrier 2	FRFOS	6	20
Carrier 2	FRLEH	16	17
Carrier 2	ITSPE	24	14
Carrier 2	ITVCE	8	12
Carrier 2	GBFXT	0	12
Carrier 3	ITNAP	38	47
Carrier 3	ITSPE	0	10
Carrier 4	BEANR	3	22
Carrier 4	ITNAP	6	22

can be summarised as below:

Allocation and ShippedTEU are master measures. 

Alloction: Sum(aggr(sum(distinct Allocation),Key1_Carrier))

ShippedTEU: sum(aggr(ShippedTEU,CntrSeal))

when a carrier is selected, top 10 location by allocation show. assuming carrier 1 is selected in the filters this table is the result. seeking help for output of the last line