Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
jdperry
Frequent Visitor

Cumulative Totals Not Working Correctly

I'm seeing some odd behavior when trying to calculate the cumulative totals of activities completed in a project schedule.  I have two relationships between the data table and the date table, an active one between the date and the As-Planned finish dates, and an inactive one between the date and the As-Built finish dates.  As you can see below, the As-Planned Cumulative Quantity appears to be computing correctly, but the AS-Built Cumulative Quantity is not.

 

I'm using the following measures on the As-Built Side:

As-Built Activity Count = CALCULATE(COUNT(AP[AB Finish]), USERELATIONSHIP(AP[AB Finish],'Date Table'[Date]))
AB Cumulative Qty = CALCULATE([As-Built Activity Count],USERELATIONSHIP(AP[AB Finish],'Date Table'[Date]),FILTER(ALL('Date Table'[Date]),'Date Table'[Date] <= MAX('Date Table'[Date])))
 
I'll also note that the AB Cumulative Qty seems to calculate okay when it's not filtered.  Once I start filtering it I start seeing the odd behavior below.  Any thoughts, anyone?  Thanks!

 

Cumulate Oddness.png

6 REPLIES 6
Ashish_Mathur
Super User
Super User

Hi,

Try this

AB Cumulative Qty = CALCULATE([As-Built Activity Count],USERELATIONSHIP(AP[AB Finish],'Date Table'[Date]),DATESBETWEEN('Date Table'[Date],MINX(ALL('Date Table'),'Date Table'[Date]),MAX('Date Table'[Date])))


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

That one didn't work either.  The cumulatives still add up to less than the sum of the AB quantity.

Hi @jdperry ,

 

Have you used" SUMX(AP,[As-Built Activity Count] " in measures, please add and try it again.

 

Best Regards,

Amy

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Amy,

 

Right now I'm using this:

 

AP Cumulative Qty = CALCULATE(SUMX(AP,[As-Planned Activity Count]),FILTER(ALLSELECTED('Date Table'[Date]),'Date Table'[Date] <= MAX('Date Table'[Date])))
 
and 
 
AB Cumulative Qty = CALCULATE(SUMX(AP,[As-Built Activity Count]),USERELATIONSHIP(AP[AB Finish],'Date Table'[Date]),FILTER(ALLSELECTED('Date Table'[Date]),'Date Table'[Date] <= MAX('Date Table'[Date])))
 
This still results in a weird result as shown above (that set of unexpected 8's in the AP Cumulative Qty and the incorrect totals in the AB Cumulative Qty.
v-xicai
Community Support
Community Support

Hi @jdperry,

 

From your screenshot above, find [AP Cumulative Qty] is not Cumulative total. You can try measures below.

 

AP Cumulative Qty= CALCULATE(SUMX(AP,[As-Planned Activity Count]),FILTER(ALLSELECTED('Date Table'[Date]),'Date Table'[Date] <= MAX('Date Table'[Date])))

 

AB Cumulative Qty = CALCULATE(SUMX(AP,[As-Built Activity Count]),USERELATIONSHIP(AP[AB Finish],'Date Table'[Date]),FILTER(ALLSELECTED('Date Table'[Date]),'Date Table'[Date] <= MAX('Date Table'[Date])))

 

Best Regards,

Amy

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Hi Amy,

 

I tried adding the allselected clause to the filter and it didn't end up changing anything.  The values in the table remained the same.  Any other thoughts?

 

Thanks!

 

-Jon

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.