cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Peculiar differences in cumulative sum measures - trying to get cumulative profit by project

I have developed a measure for profit (SUM(revenue) - SUM(cost), as expected) that works just fine.  As expected, the measure aggregates according to the aggregate rows in output tables (e.g. if Project and Period is shown then the measure sums over Project and Period, but if only Period is shown then the measures sums over Period only).

 

The issue is that a measure I have made for CUMULATIVE profit does not work in the same consistent manner.  The columns in output tables affect how it works, yielding different - and wrong - results depending on columns chosen.

 

I have tried two different measures as  follows:

Cumulative Profit (with project constraint)

Cumulative 1 = CALCULATE([Profit], FILTER(ALLSELECTED(Table), Table[Period]<=MAX(Table[Period])))

 

Cumulative Profit (no project constraint)

Cumulative 2 = CALCULATE([Profit], FILTER(ALLSELECTED(Table), Table[Period]<=MAX(Table[Period]) && Table[Project] = MAX(Table[Project])))

 

Whether each gives the right answer depends on whether the output table shows project or not.  These mock-ups show the behaviour:

 

Capture3.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Correct (desired) cumulative balances are in green, wrong answers are in red. 

 

It is weird that one solution works when the Project is a column in the output table, and one solution works when it is not.  For the Profit measure, it works whatever the columns in the output table.

 

Anyone know how to fix it?

 

Cheers

 

John

2 REPLIES 2
Super User IV
Super User IV

@JohnYeldham , refer if this can help

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Super User IV
Super User IV

@JohnYeldham - Would love to have some sample data to test with in order to see if I could come up with a solution that works for both. Can you post some sample data?

 

Overall though, I am not necessarily suprised, measures are finicky when it comes to context. Just think about the measures total problem. You could put them into a single measure like this:

Cumulative Profit =
  IF(
    ISINSCOPE('Table'[Project]),
    [Cumulative 1],
    [Cumulative 2]
  )

---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors