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