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 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

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"))

3 Replies

  • 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.



  • Enzilmenz's avatar
    Enzilmenz
    Frequent Visitor
    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"))