Summation of rows based on Dropdown selection in Power BI

Question

I have a dropdown in Power BI that contains different project name such as Project One, Two, Three. I have included one formula to bring forecast value which is:

Forecast = Chase * Target%

I have created one measure that calculates forecast. The dataset contains weekly based data for Chase and Target %. For example week 1 (Jan 01-Jan 08) Chase will be 30 and target % as 10 hence the forecast for Week 1 is 3 (30*10%)

When I select one project from dropdown list e.g. "Project One" I see the forecast value populating correctly. Same goes if I select only one project from dropdown list .

The issue arises when I select multiple projects and then the forecast value brings the maximum value instead of bringing summation to the values of all weeks of all projects.

Question: What exactly is causing the issue?

Answer 1

Now I understand your requirement from your comments. You can achieve this through 2 step as explained below-

Step-1: Create a custom column in your data source as below-

row_level_forecast = finetarget[chase]/100.00 * finetarget[target]

Step-2: Create the final Measure as below-

forecast = sum(finetarget[row_level_forecast])

Now, use measure "forecast" in the report. This should give you the desired output.

ISSUE-2: From your comments

If I understand correct, you are talking about a case where you are concern about values in columns I marked red in the below picture-

If I am correct with my understanding, you wants to fill week-3 values for Project-1 with 80/70 and for Project-2 100/90. If this is ok, just follow these following steps.

Step-1: Go to EDIT mode clicking "Transform Data" option and select the table you wants to adjust data.

Step-2: Sort your data first for project_name (ascending) then week (ascending). The output will be also as shown in the above image.

Step-3: Select column "chase" in the table and click Fill>>Down option.

Step-4: Repeat step 3 for column"target" as well.

The final output should be as below. Just move back to main report by clicking "Close and Apply". Data should be now as expected in your report.