Help me please ;)

[RonaldvdH]

I need to upgrade my current measure to include a variable

Cumulatieve HC geforecast =

CALCULATE ([Totaal geforecast aantal HC];

FILTER(

ALL('Forecast FttH');

'Forecast FttH'[Week]<=MAX('Forecast FttH'[Week])

))

In the table it measures a total value but in this table are two different projects, Project A and Project B and i need this formula to  work with this variable.

Ive already tried to add and AND variable to the formula but that doesn't work. The table has the data in this format

Project          Weeknr       Forecast activity A      Forecast activity B

Project A       2020-02            200                                100

Project A       2020-03            200                                100

Project B       2020-02            300                                 50

Project B       2020-03            300                                 50

Project A       2020-04            100                                 200

Project B       2020-04            200                                 150

The measure should take in account that there are 2 projects to calculate and not just 1 and maybe i need a new table to combine data or results of a formula but im not sure so i hope you guys can help me out here

When i use a Slicer and select Project A the measure should only measure based on Project A and not combine Project A and B but i think its because the formula above doesn't have the variable in it to differentiate between projects

Does anyone understand what im talking about or do you need more info

[az38]

Hi @RonaldvdH 

maybe this will help:

Cumulatieve HC geforecast =
CALCULATE ([Totaal geforecast aantal HC];
FILTER(
ALL('Forecast FttH');
'Forecast FttH'[Week]<=MAX('Forecast FttH'[Week]) && 'Forecast FttH'[Project] = SELECTEDVALUE('Forecast FttH'[Project])
))
 

do not hesitate to give a kudo to useful posts and mark solutions as solution

[RonaldvdH]

@az38  thanks for your quick response and it looks like it works but i need to examine your solution further.

What i can see is that is works fine when i select just 1 project but when i select both the measure doesn't seem to work

or in the very least the result is very strange

Any ideas ?

[az38]

@RonaldvdH 

if you select both project what your desired result? project A + projcet B or it still should be divided?

do not hesitate to give a kudo to useful posts and mark solutions as solution

[RonaldvdH]

Then it should be combined to 1 result

Selection           Value

Project A             200

Project B             300

Project C             200

Project D             100

Project A+B         500

Project A+B+C    700

Select All              800

[az38]

@RonaldvdH 

so, let us know if my measure will not work

do not hesitate to give a kudo to useful posts and mark solutions as solution

[RonaldvdH]

I just did 🙂 because the result isn't correct when you select more then 1 project

[v-xuding-msft]

Hi @RonaldvdH ,

When you select multiple projects in slicer, what results should be shown while the project is in the same week? It should be calculated cumulative values together or separately based on the projects? It should show the total values in the total row like below? If so, you just need to create a new measure by SUMX function and use the formula that @az38 suggested. Or it should show the total values in each row? 

Measure 1 = SUMX('Forecast FttH',[Measure])

Please show your expected results. Then we will understand clearly.

Best Regards,

Xue Ding

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

[RonaldvdH]

@v-xuding-msft  and @az38 

ive altered the measure to:

Test1 = SUMX('Forecast FttH';[Totaal geforecast aantal HC])

and changed the formula provided by @az38 to:

Cumulatieve HC geforecast =

CALCULATE ([Test1];

FILTER(

ALL('Forecast FttH');

'Forecast FttH'[Week]<=MAX('Forecast FttH'[Week]) && 'Forecast FttH'[Hoofdproject] = SELECTEDVALUE('Forecast FttH'[Hoofdproject])

))

But unfortunately there was no difference in the outcome, ive added a few picture to hopefully clarify what im trying to do

Project A, the result (red line) is correct (200 a week makes a line with values 200, 400, 600 etc)

Project B, the result (red line) is correct (350 a week makes a line with values 350, 350, 450 etc)

Based upon the values in the bars

Project A+B, the red line is strange because why would it start at 5800 in wk 2020-38 if both Project A and B start in wk 2020-02 and 2020-07

The result should be the cumulative value (as a line) of Project A and B starting from wk 2020-02 until (in this case) 2020-37

[az38]

@RonaldvdH 

whats your desired output based on your data example?

whats output you expect for slicer A and for slicer A+B? How it should look like?

do not hesitate to give a kudo to useful posts and mark solutions as solution