cumulative total not working

[enzo88]

Hi all,

i am trying to make a measure that calculates the cumulative total in a dynamic way, which means that it has to consider all the filters applied on the page I am currently in.

I have come up with the following:

Tot Value Pareto=

var currentvalue = [Tot Value €]

var cumulativeExpected = sumx(filter(

summarize(allselected('Table'),'Table'[Vendor Code],"Tot Value", [Tot Value €]),[Tot Value]>=currentvalue)
,[Tot Value])

RETURN

cumulativeExpected

Measure almost works, but I do not understand why the first row is not taken into consideration.

Thanks in advance

Enzo

[Enzilmenz · Accepted solution]

I have solved it, i had to remove the summarize function and i have also used a different approach with RANKX.

I also changed the final output in order to give me an abc classification based on a pareto model 70,20,10.

Tot value pareto = 

var currentline = rankx(allselected('PO Schedule'[Vendor Code]),[Tot Value €],,DESC)

var totalvalue=sumx(ALLSELECTED('PO Schedule'[Vendor Code]),[Tot Value €])

var cumulateExpected = sumx(

    FILTER(

        ALLSELECTED('PO Schedule'[Vendor Code]),RANKX(ALLSELECTED('PO Schedule'[Vendor Code]),[Tot Value €],,DESC)<=currentline),

        [Tot Value €]

)

var pareto=DIVIDE(cumulateExpected,totalvalue,0)*100

RETURN

if(pareto<=70,"A",if(pareto<=90,"B","C"))

[Mikelytics]

Hi @enzo88 

Can you please try the following?

Total Value Pareto=

var currentvalue = [Tot Value €]

var cumulativeExpected = 
sumx(
   summarize(
      allselected('Table'),
      'Table'[Vendor Code],
      "Tot Value", [Tot Value €]
   ),
   IF([Tot Value]>=currentvalue,[Tot Value],0)
)

RETURN
cumulativeExpected

IF it should work it might be possible to optimize the if clause with another variable. But first priority should be getting the right result.

Best regards

Michael

Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.

[Enzilmenz]

Hello,

i have tried the suggestion and use an if condition but i get this now:

it seems that "Tot Value" of the summarize table is set at 0 in the first iteration, i don't understand why it does not take the first row as input.