Recursive totals for projects for different Sites

nico.swartz · November 12, 2020, 6:55am

Good day, I need some assistance with a problem, please. It is probably because I still don’t fully understand filtering but here goes:

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:

Reduced Cost VALUE = 
SUMX(
    SUMMARIZE(
      'Projects Percentages',
      'Projects Percentages'[Project No],
      'Projects Percentages'[Project],
      'Projects Percentages'[Site],
      'Projects Percentages'[Project Category],
        "AB",VAR PN = 'Projects Percentages'[Project No] 
            RETURN
            PRODUCTX(
                FILTER(
                    ALL( 'Projects Percentages' ),
                'Projects Percentages'[Project No] <= PN
                ),
                [1- RS%S]
            ) * [BAU_E]
        ),
    [AB]
)

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.

EnterpriseDNA · November 16, 2020, 2:35am

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.

I also suggest that you check the forum guideline https://forum.enterprisedna.co/t/how-to-use-the-enterprise-dna-support-forum/3951. Not adhering to it may sometimes cause delay in getting an answer.

Melissa · November 16, 2020, 9:44am

Hi @nico.swartz,

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.

I hope this is helpful.

nico.swartz · November 16, 2020, 11:14am

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:

Here is a mockup of the actual results required:

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.

I’ll try to do a sample PBIX file.

Melissa · November 16, 2020, 11:33am

Hi @nico.swartz,

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.

nico.swartz · November 16, 2020, 3:11pm

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.

Not sure if this makes sense?

Melissa · November 16, 2020, 3:22pm

Hi @nico.swartz,

Note that this will be my final attempt to assist you - without a sample PBIX, there is just not much more I can do for you.

In my pattern from post #3 adjust the sTable variable to:

VAR sTable = 
    CALCULATETABLE( 'Sample',
        'Sample'[Site] IN VALUES( 'Sample'[Site] ),
        ALLSELECTED()
    )

The rest of the pattern I’m confident you can translate to your data, like you’ve done before.
All the best.

.

and to correct the total row…

Costs after Saving (Measure) v3 incl Total = 
VAR vTable =
    ADDCOLUMNS( 'Sample',
        "@Value", [Costs after Saving (Measure) v3]
    )
RETURN

IF( HASONEVALUE( 'Sample'[Project] ),
    [Costs after Saving (Measure) v3],
    SUMX( vTable, [@Value] )
)

nico.swartz · November 17, 2020, 2:37pm

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.

Melissa · November 17, 2020, 3:59pm

Hi @nico.swartz,

Glad to hear that worked well for you.

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.

https://info.enterprisedna.co/dax-function-guide/calculatetable/

Go to modeling on the Ribon, select New Table and past in this code:

sTable = 
    CALCULATETABLE( 'Projects Percentages',
        'Projects Percentages'[Site] IN { "Site 2" },  //VALUES( 'Sample'[Site] ),
        ALLSELECTED()
    )

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.

I hope this is helpful.

nico.swartz · November 17, 2020, 9:07pm

Thanks again.