KPI: sum by Rank and Set analysis

[Vox]

Hi all
this's my first post.
I searched for this topic on other post but could't find a solution which works properly.
I hope to be clear enough and to find a solution.

I've a database with
- Project (some record may be blank (1 chr), some with values)
- Items
- Costs

I'm trying to create a KPI which displays the sum of the top 3 items order by Cost descending, ONLY on those records with:
- a Project which is valid (not blank),
- items like '10*'

It should be something similar to this
= Sum(
{< [Project Code] -= {" *"}
, [Item Code] = {"10*"}
>}
[Cost]
)
However this is not suitable because works on full database. I need to add a Rank

The problem is I can't get the sum of the restricted list

List Original (not sorted):
Project Item Cost
Prj_1 10400 30.157
4892 29.868
Prj_3 11563 23.851
20303 6.738
Prj_2 10562 9.868
Prj_4 10678 33.200

List sorted by Cost:
Project Item Cost
Prj_4 10678 33.200
Prj_1 10400 30.157
4892 29.868
Prj_3 11563 23.851
Prj_2 10562 9.868
20303 6.738

Top 3 (only related to project not blank, item like 10*)
Project Item Cost
Prj_4 10678 33.200
Prj_1 10400 30.157
Prj_2 10562 9.868

I want to get the total 73.225 (33.200 + 30.157 + 9.868)

I tried using all possible variations/combinations of Sum, Aggr, Rank, ... without success
The most similar good formula is

sum(Aggr(if(Rank(Sum(Cost),4,1) <=3, Sum(Cost), 0), Project_Item))

but it's not so good because I need to work on a set analysis. And if I enter a set analysis in it, it doesn't work

(futhermore, intstead of the number 3, I'll add a variable to let user to choose, with a slicer, it's own list of record to evaluate)

Did someone already occurred into same problem?
Does someone has any good idea?

Here below a prepared source code if someone wants to copy/past for his/her test

Project_Item_Cost:
Load * Inline [
Project,Item,Cost
Prj_1,10400,30.157
,4892,29.868
Prj_3,11563,23.851
,20303,6.738
Prj_2,10562,9.868
Prj_4,10678,33.200
];

Thanks in advance for your time

[jonathandienst]

Perhaps something like this:

Sum({<[Project Code] -= {''}, [Item Code] = {"10*"}>}
	Aggr(
		If(Rank(
			Sum({<[Project Code] -= {''}, [Item Code] = {"10*"}>} Cost), 4, 1) <= 3, 
				Sum({<[Project Code] -= {''}, [Item Code] = {"10*"}>} Cost), 0
		)
	, Project_Item)
)

(Assumes that the excluded project codes are empty strings, if they are a single space, insert a space    ...-= {' '}...)

[Vox · Accepted solution]

Thank you very much for your help.

It works properly.

My mistake was due to wrong usage of aggregation: I used [Project] & [Item] and I got display error.
Then I created a new concatenated field [Project_Item] in load script (load ... [Project] & [Item] AS [Project_Item]...) and now it works fine !

I post here below the final code:

Sum({<[Project] -= {''},

      [Item]     = {"10*"}

    >}

    Aggr(

         If(

            Rank(

                 Sum({<[Project] -= {''},

                       [Item]     = {"10*"}

                     >}

                     [Cost])

                 , 4, 1) <= 3

            , Sum({<[Project] -= {''},

                    [Item]     = {"10*"}

                  >}

                  [Cost])

            , 0

           )

    , [Project_Item])

   )

Bye