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?
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.