Skip to main content
cancel
Showing results for 
Search instead 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.

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

 

 

thumbnail_image001.png

 

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

 

Thanks in advance

Enzo

1 ACCEPTED SOLUTION
Enzilmenz
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)*100

RETURN

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

View solution in original post

3 REPLIES 3
Enzilmenz
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)*100

RETURN

if(pareto<=70,"A",if(pareto<=90,"B","C"))
Mikelytics
Resident Rockstar
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.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.

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

Hello,

 

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

 

Enzilmenz_0-1675243771887.png

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.



Helpful resources

Announcements
Microsoft Fabric Learn Together

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

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors