Forum Discussion

enzo88's avatar
enzo88
Frequent Visitor
3 years ago
Solved

cumulative total not working

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 ...
Need Help
  • Enzilmenz's avatar
    Enzilmenz
    3 years ago

    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's avatar
Mikelytics
Icon for Resident Rockstar rankResident Rockstar
3 years ago

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's avatar
    Enzilmenz
    Frequent Visitor
    3 years ago

    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.