cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Regular Visitor

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

Enzo

1 ACCEPTED SOLUTION
Frequent Visitor

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)*100RETURNif(pareto<=70,"A",if(pareto<=90,"B","C"))`
3 REPLIES 3
Frequent Visitor

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)*100RETURNif(pareto<=70,"A",if(pareto<=90,"B","C"))`
Resident Rockstar

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.

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!
Frequent Visitor

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.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors