We have a cost to operations (BAU_E) under normal working conditions (Behaviour as usual). Certain projects get implemented and they realize a certain saving % to the cost. So, BAU_E * (1 - Savings% ) = the cost after project implementation. if a new project gets implemented it also realizes a saving % but the cost of production has been reduced so the cost after the second projects gets implemented is the new reduced cost value x (1- savings% of P2) (indicated in red above)
I created the following DAX measure that does this:

I use the project numbers in my calculations. It works perfectly if the projects numbers start at 1. The projects, however, are implemented on different sites and I might only be looking at projects 23 through 28 (Different sites). The measure should not do the calculation starting at project 1 but starting at project 23 and working through to project 28. I am not sure how to do the filtering to achieve this per project?
( FILTER(
ALL( ‘Projects Percentages’ ),
‘Projects Percentages’[Project No] <= PN
).

The highlighted value should be: 2233947 * 0.8442 = 1 885 898 and not 648 564.95

Please could anyone assist? As I say it is probably because I am still learning DAX and am missing some fundamentals.

Hi @nico.swartz, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

Use the forum search to discover if your query has been asked before by another member.

When posting a topic with formula make sure that it is correctly formatted to preformatted text </>.

Use the proper category that best describes your topic

Provide as much context to a question as possible.

Include demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

Without a sample PBIX file and a mock up of the desired result, it’s always difficult to provide support.
I went back to your previous topic, related to this one and created this measure.

That does seem to work for a subset of the data when I filter the visual down SO you’ll have to amend the sTable variable to contain only the Project numbers that should be included in the calculation.

Thanks for your reply Melissa. It is difficult to send a PBIX file as the client data is sensitive and confidential. I have however attempting to create a PBIX file and mockup of results. So here goes:

We have a lookup table that has :

The fact table gives the yearly cost per site (Among other values).

After calculations, I end up with the values as above. This is the Dax measure that I implemented to get the result:

The question is actually how to determine the Project number for the first project for a site so I can filter the starting point for the calculation. (Index or project number )

For Site 2 to start with project 7 (and not project 1) with the calculation. Getting the filter values correct.
FILTER(
ALL( ‘Projects Percentages’ ),
‘Projects Percentages’[Project No] <= PN &&
‘Projects Percentages’[Project No] >= min( [Project No] for the site - site 2 above ) or something simillar.

Looking at it I think all you have to do is incorporate a grouping on Site within the sTable variable.
If you need further assistance please provide a sample PBIX with a mock up of the desired results.

Not sure how to get the project number for the 1st project for each site (In total about 60 sites). Need to do the calculation (If look at the table above) for projects 1- 6 and then, for the next site, projects 7 - 12.

Thanks again for your support. The measure is now getting the correct results.
Can you please explain how the calculate table function works here? It works correctly but I cannot get my head around it.

Here you can find the documentation on CACULATETABLE, just like CALCULATE it let’s you modify the filter context but in this case for a table and not a scalar value.

You’ll find that your source table is filtered down to only rows where [Site] = “Site 2”
In the measure the Site is identified by using VALUES( ‘Projects Percentages’[Site] )
ALLSELECTED is required to return all the rows that meet the previous condition, ignoring filtering from the matrix itself without removing the external filter context.