Cannot compute a cumulated value based on a dynamic index measure (not a column)

[Anonymous]

Hello everyone,

I hope you are well, I will be really grateful for the person who can help me !

Let's consider the following data :

table : item (sample)

Item_Desc	Date	Quantity	Unit_Cost	Inventory_Value
01591A | item3	05/01/2022	1	5715	5715
01591A | item3	13/01/2022	-2	5715	-11430
01591A | item3	02/02/2022	3	5715	17145
01591A | item3	03/02/2022	-2	11430	-11430
01591A | item3	08/02/2022	-2	5715	-11430
01591A | item3	10/02/2022	4	11430	22860
01591A | item3	16/02/2022	-1	11430	-5715
01591A | item3	22/02/2022	2	11430	11430
01591A | item3	23/02/2022	-2	5715	-11430
01591A | item3	02/03/2022	3	5715	17145
01464D | item1	04/03/2022	0	632,5	0
01464D | item1	08/03/2022	-2	1265	-1265
01591A | item3	08/03/2022	3	5715	17145
01591A | item3	16/03/2022	3	5715	17145
02161A | item4	18/03/2022	59	135,2397	7979
02161A | item4	22/03/2022	-2	270,4794	-270
02161A | item4	23/03/2022	-2	270,4794	-270
02161A | item4	24/03/2022	-2	135,2397	-270
02161A | item4	28/03/2022	-3	135,2397	-406
01591A | item3	30/03/2022	3	5715	17145
02161A | item4	01/04/2022	-2	135,2397	-270
01591A | item3	04/04/2022	3	5715	17145
021612A | item4	05/04/2022	-2	135,2397	-270
02221B | item5	05/04/2022	29	220,9791	6408
01464D | item1	06/04/2022	-2	1265	-1265
02161A | item4	06/04/2022	-2	135,2397	-270
02221B | item5	06/04/2022	29	220,9791	6408
01464D | item1	07/04/2022	41	1265	25933
01591A | item3	07/04/2022	-2	5715	-11430
02161A | item4	07/04/2022	-5	135,2397	-676
02161A | item4	11/04/2022	-4	135,2397	-541
01591A | item3	12/04/2022	3	5715	17145
02161A | item4	12/04/2022	-2	135,2397	-270
02161A | item4	14/04/2022	-3	135,2397	-406
02221B | item5	14/04/2022	-2	220,9791	-442
02221B | item5	15/04/2022	-2	441,9582	-442
02161A | item4	19/04/2022	-2	135,2397	-270
02221B | item5	20/04/2022	-2	220,9791	-442
02161A | item4	22/04/2022	-2	135,2397	-270
01464D | item1	26/04/2022	-2	1265	-1265
02161A | item4	26/04/2022	-5	270,4794	-676
02161A | item4	27/04/2022	-2	270,4794	-270
02161A | item4	28/04/2022	99	135,2397	13389
022219B | item5	28/04/2022	-2	441,9582	-442
01591A | item3	29/04/2022	-2	11430	-11430
...	...	...	...	...

schema associated

The dynamic index is obtained by the following measure :

Dynamic index =

VAR _index = RANKX(ALLSELECTED('Item'[Item_Desc]),calculate(SUM('Item'[Inventory_Value])),,DESC,DENSE)

return

_index

What I want is to compute for each row the cumulated "Inventory_Value" based on the measure "dynamic_index" .

Results expected 

This will enable me to know dynamically (by switching between YearMonth) which items represent 80% of the total "Inventory_Value".

Thank you

[lbendlin · Accepted solution]

You don't need the dynamic index for that.  All you need to do is accumulate all values that are bigger or equal to the current value.

Cumul = 
var a = sum('Item'[Inventory_Value])
var b = CALCULATETABLE(SUMMARIZE('Item','Item'[Item_Desc],"s",sum('Item'[Inventory_Value])),ALLSELECTED())
var c = filter(b,[s]>=a)
return sumx(c,[s])

and you can do the percentage the same way

Cumul % = 
var a = sum('Item'[Inventory_Value])
var b = CALCULATETABLE(SUMMARIZE('Item','Item'[Item_Desc],"s",sum('Item'[Inventory_Value])),ALLSELECTED())
var c = filter(b,[s]>=a)
return divide(sumx(c,[s]),sumx(b,[s]),0)

[Anonymous]

Oh my god, that's so amazing! This code is so elegant and helps me solve the dynamic Pareto problem on the sub-category!!!!

[Anonymous]

To implement real-time filtering out of the Pareto sub-category on category, you only need to do the following operations.

Cumul % = 
var a = sum('Item'[Inventory_Value]) 
var b = CALCULATETABLE(SUMMARIZE('Item','Item'[Item_Desc],"s",sum('Item'[Inventory_Value]),REMOVEFILTERS('Item'[Sub_Category])) 
var c = filter(b,[s]>=a) 
return 
divide(sumx(c,[s]),sumx(b,[s]),0)

[Anonymous]

Thank you very much !

It works well for me.