cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper IV
Helper IV

Help me please ;)

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
9 REPLIES 9
Highlighted
Super User II
Super User II

Re: Help me please ;)

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


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

Highlighted
Helper IV
Helper IV

Re: Help me please ;)

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

Highlighted
Super User II
Super User II

Re: Help me please ;)

@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


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

Highlighted
Helper IV
Helper IV

Re: Help me please ;)

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

Highlighted
Super User II
Super User II

Re: Help me please ;)

@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


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

Highlighted
Helper IV
Helper IV

Re: Help me please ;)

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

Highlighted
Super User II
Super User II

Re: Help me please ;)

@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


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

Highlighted
Community Support
Community Support

Re: Help me please ;)

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])

3.PNG

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.

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.
Highlighted
Helper IV
Helper IV

Re: Help me please ;)

@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_A.png
 
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_B.png
 
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
 
Project_A_and_B.png
 
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

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors