Ranking by Sum in a given limit

[SilviuSaulea]

Hi,

I have a table like this:

Id	Task	Priority	Effort
1	AA	2	2
2	BB	0	1
3	CC	1	3
4	DD	4	3
5	EE	1	2
6	FF	0	2
7	GG	3	2

I need to calculate session order column grouping rows over a max value of cumulated value of Effort ordered by priority then by Id.

Let's presume that max effort accepted in session is 4 (this max value will be a prarameter).

The result should be:

Id	Task	Priority	Effort	Session
2	BB	0	1	Session 1
6	FF	0	2	Session 1
3	CC	1	3	Session 2
5	EE	1	2	Session 3
1	AA	2	2	Session 3
7	GG	3	2	Session 4
4	DD	4	3	Session 5

I'm really new to Dax and I have no ideea for this.

Any help will be much apreciated!

[SilviuSaulea]

I reiterate the request to be more clear. Let's asume the next data:

Id	Task	Priority	Effort	Desired result
1	AA	1	1	Session 001 (Cumulated 4)
2	BB	2	1	Session 001 (Cumulated 4)
3	CC	3	1	Session 001 (Cumulated 4)
4	DD	4	1	Session 001 (Cumulated 4)
5	EE	5	1	Session 002 (Cumulated 3)
6	FF	6	2	Session 002 (Cumulated 3)
7	GG	7	2	Session 003 (Cumulated 4)
8	HH	8	2	Session 003 (Cumulated 4)
9	II	9	2	Session 004 (Cumulated 2)
10	JJ	10	3	Session 005 (Cumulated 3)

So the quest is how to obtain a kind of rank based on SUM of column "Effort" until a specified accepted threshold (value 4 for the given data). The order of summing is by priority and if the sum of previous + current > threshold then current position goes tho the next session and curent sesion cumulated value starts from the current effort.

@Anonymous any other ideea?

[Anonymous]

Hi  @SilviuSaulea ,

Here are the steps you can follow：

1. Create calculated column.

Result =
var _sumGroup=
SUMX(
    FILTER(ALL('Table'),'Table'[Priority]=EARLIER('Table'[Priority])),[Effort])
var _sumGroupCum=
SUMX(
    FILTER(ALL('Table'),
    'Table'[Priority]=EARLIER('Table'[Priority])&&
    'Table'[Effort]<=EARLIER('Table'[Effort])),[Effort])
var _rank=
RANKX(
    'Table','Table'[Priority],,ASC,Dense)
var _if=
IF(
    _rank=1&&_sumGroup<=4&&_sumGroupCum<=4,[Rank],
    IF(
       _sumGroup>4&&_sumGroupCum<=4,_rank+1,_rank))
return
"Session"&" "&_if

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[SilviuSaulea]

Superb, we are almost there.

I change the priority to show you something:

I think rank over priority is not enough. From the sample above I don't want to break session if the priority change.

Id 2 should be combined with Id 6 even if they are not on the same priority. Important is how much I cumulate on the curent session order by [Priority,Id] and not to oversize the limit 4.

Look on the next sample to be more accurate:

[Anonymous]

Hi  @SilviuSaulea ,

It's not very clear how Session2 and Session3 are obtained, can you describe the calculation logic of [Session] with a picture or text and we can help you better.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SilviuSaulea]

Let's start with Session 1:

we have in order records with id 2 and 6 with efort 1 + 2 = 3 <= limit 4. Prety clear.

Now the next one is id 3 with effort 3 and then id 5 with effort 2. Effort 3 + 2 > 4 then id 3 will have the next session of its own -> Session 2. The condition is that the cumulated consecutive effort to be <= 4. Of course in case of a single effort > 4 then this will have his own session then go forward.

So we have consumed in order Id's 2, 6 and 3 then we just continue the algorithm on the rest.

Id 5 and 1 will group in Session 3, id 7 cannot be gruped with id 4 (effort 2 + 3 > 4) then id 7 will take the next (Session 4) then id 4 will take the last Session 5.

Thank you for being interested 🙂

Silviu