topic Re: Cannot compute a cumulated value based on a dynamic index measure (not a column) in DAX Commands and Tips

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

Anonymous — Thu, 19 Jan 2023 15:26:44 GMT

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

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

lbendlin — Tue, 23 Aug 2022 01:36:49 GMT

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)

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

Anonymous — Tue, 23 Aug 2022 07:57:13 GMT

Thank you very much !

It works well for me.

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

Anonymous — Tue, 21 Mar 2023 03:09:39 GMT

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

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

Anonymous — Tue, 21 Mar 2023 03:21:54 GMT

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)