Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I am looking for a formula to apply a percent increase to my [Cycle Time (Hrs)*] based on the task selected by a parameter and return all other [Cycle Time (Hrs)*] without the percent increase. The parameter is [Selected Task] and the 'Percent Increase to Cycle Time'[Percent Increase to Cycle Time Value] is a percentage parameter as well. I have the following formula, but it doesn't work.
Solved! Go to Solution.
Ah. I think the total may be correct but for the individual rows, we need to keep the filter context from the visual so that not all rows are the same value.
Try this:
Adjusting 1 Cycle Time (hrs) =
VAR SelectedTasks = VALUES ( Task[Task] )
RETURN
CALCULATE (
SUM ( Sheet1[Cycle Time (Hrs)*] ),
KEEPFILTERS ( Sheet1[Task] IN SelectedTasks )
) * ( 1 + [Percent Increase to Cycle Time Value] )
+ CALCULATE (
SUM ( Sheet1[Cycle Time (Hrs)*] ),
KEEPFILTERS ( NOT ( Sheet1[Task] IN SelectedTasks ) )
)
You'll need a separate table for your task parameter.
This version should be more efficient than SUMX:
VAR SelectedTasks = VALUES ( ParamTask[Task] )
RETURN
CALCULATE (
SUM ( Sheet1[Cycle Time (Hrs)*] ),
Sheet1[Task] IN SelectedTasks
) * ( 1 + 'Percent Increase'[Percent Increase] )
+ CALCULATE (
SUM ( Sheet1[Cycle Time (Hrs)*] ),
NOT ( Sheet1[Task] IN SelectedTasks )
)
Thank you for the solution. I applied your formula, however it's now returning the selected ParamTask[Task]
Hmm. Does your task parameter table have a relationship with Sheet1? (Parameter tables should be unrelated.)
Just now removed the relationship. Now it's summing all [Cycle Time (Hrs)*] and returning just that total value for each Task.
I don't think I can easily diagnose without seeing more context, e.g., exactly which columns you're using in all the relevant visuals and the relationship diagram (etc.).
The table on the left are the cycle times per each task (the lowest level of detail). The table on the right is with the formula to apply the percent increase to just 1 task.
Ah. I think the total may be correct but for the individual rows, we need to keep the filter context from the visual so that not all rows are the same value.
Try this:
Adjusting 1 Cycle Time (hrs) =
VAR SelectedTasks = VALUES ( Task[Task] )
RETURN
CALCULATE (
SUM ( Sheet1[Cycle Time (Hrs)*] ),
KEEPFILTERS ( Sheet1[Task] IN SelectedTasks )
) * ( 1 + [Percent Increase to Cycle Time Value] )
+ CALCULATE (
SUM ( Sheet1[Cycle Time (Hrs)*] ),
KEEPFILTERS ( NOT ( Sheet1[Task] IN SelectedTasks ) )
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
27 | |
20 | |
15 | |
12 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
18 |