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 Power BI Experts. I would like to ask your help please for my bar chart in the bottom.
I want to to reflect in the bottom bar chart the remaining percent left. As you can see in the upper bar chart it is more than 100% (in my chart I use decimal) which 1. 4.
I want to reflect the in the bottom chart that the remaining percent left is -40% (or -0.4). As you can see, only the 40% in the upper chart is subtracted to 100%. The bottom chart should supposed to be calculate the 100% and the 40%. In mathematical sense, the computation is
100% maximum percent - 40% percent Project A - 100% in Project B = -40%
I want to know how to put a DAX to have a result of the "Remaining Percent" column in my data so I cannot put it manually.
Once any of the project ends, it will subtract to the total assigned percent.
Month Assigned Resource Project Start Date End Date Maximum Percent Capacity Assigned Remaining Percent
01/07/2022 | Employee A | Project A | 22/07/2022 | 31/07/2023 | 100% | 40% | 60% |
01/08/2022 | Employee A | Project A | 22/07/2022 | 31/07/2023 | 100% | 40% | 60% |
01/09/2022 | Employee A | Project A | 22/07/2022 | 31/07/2023 | 100% | 40% | 60% |
01/10/2022 | Employee A | Project A | 22/07/2022 | 31/07/2023 | 100% | 40% | 60% |
01/11/2022 | Employee A | Project A | 22/07/2022 | 31/07/2023 | 100% | 40% | 60% |
01/12/2022 | Employee A | Project A | 22/07/2022 | 31/07/2023 | 100% | 40% | 60% |
01/01/2023 | Employee A | Project A | 22/07/2022 | 31/07/2023 | 100% | 40% | 60% |
01/02/2023 | Employee A | Project A | 22/07/2022 | 31/07/2023 | 100% | 40% | 60% |
01/03/2023 | Employee A | Project A | 22/07/2022 | 31/07/2023 | 100% | 40% | 60% |
01/04/2023 | Employee A | Project A | 22/07/2022 | 31/07/2023 | 100% | 40% | 60% |
01/05/2023 | Employee A | Project A | 22/07/2022 | 31/07/2023 | 100% | 40% | 60% |
01/06/2023 | Employee A | Project A | 22/07/2022 | 31/07/2023 | 100% | 40% | 60% |
01/07/2023 | Employee A | Project A | 22/07/2022 | 31/07/2023 | 100% | 40% | 60% |
01/09/2022 | Employee A | Project B | 01/09/2022 | 01/09/2023 | 100% | 100% | -40% |
01/10/2022 | Employee A | Project B | 01/09/2022 | 01/09/2023 | 100% | 100% | -40% |
01/11/2022 | Employee A | Project B | 01/09/2022 | 01/09/2023 | 100% | 100% | -40% |
01/12/2022 | Employee A | Project B | 01/09/2022 | 01/09/2023 | 100% | 100% | -40% |
01/01/2023 | Employee A | Project B | 01/09/2022 | 01/09/2023 | 100% | 100% | -40% |
01/02/2023 | Employee A | Project B | 01/09/2022 | 01/09/2023 | 100% | 100% | -40% |
01/03/2023 | Employee A | Project B | 01/09/2022 | 01/09/2023 | 100% | 100% | -40% |
01/04/2023 | Employee A | Project B | 01/09/2022 | 01/09/2023 | 100% | 100% | -40% |
01/05/2023 | Employee A | Project B | 01/09/2022 | 01/09/2023 | 100% | 100% | -40% |
01/06/2023 | Employee A | Project B | 01/09/2022 | 01/09/2023 | 100% | 100% | -40% |
01/07/2023 | Employee A | Project B | 01/09/2022 | 01/09/2023 | 100% | 100% | 0% |
01/08/2023 | Employee A | Project B | 01/09/2022 | 01/09/2023 | 100% | 100% | 0% |
01/09/2023 | Employee A | Project B | 01/09/2022 | 01/09/2023 | 100% | 100% | 0% |
Solved! Go to Solution.
Hi @third_hicana ,
Please pay attention to the position of the parentheses.
Measure =
VAR _mindate =
MINX ( ALLSELECTED ( Sheet1 ), Sheet1[month] )
VAR _vapacityvalue =
CALCULATE (
MAX ( Sheet1[Capacity Assigned] ),
FILTER ( ALL ( Sheet1 ), Sheet1[month] = _mindate )
)
VAR _project =
CALCULATE (
MAX ( Sheet1[project] ),
FILTER ( ALL ( Sheet1 ), Sheet1[month] = _mindate )
)
VAR _1result =
IF (
MAX ( Sheet1[project] ) = _project,
MAX ( Sheet1[Maximum Percent] ) - MAX ( Sheet1[Capacity Assigned] ),
IF (
MAX ( Sheet1[project] ) <> _project,
MAX ( Sheet1[Maximum Percent] ) - MAX ( Sheet1[Capacity Assigned] ) - _vapacityvalue,
BLANK ()
)
)
VAR _before =
CALCULATE (
MAX ( Sheet1[end date] ),
FILTER ( ALL ( Sheet1 ), Sheet1[rankx] = SELECTEDVALUE ( Sheet1[rankx] ) - 1 )
)
VAR _year =
YEAR ( _before )
VAR _month =
MONTH ( _before )
VAR _timebigthan =
IF (
MAX ( Sheet1[year] ) = _year
&& MAX ( Sheet1[month_column] ) >= _month,
1,
0
)
RETURN
IF (
MAX ( Sheet1[_countproject] ) <> 1
&& MAX ( Sheet1[rankx] ) = 1,
_1result,
IF (
MAX ( Sheet1[_countproject] ) <> 1
&& MAX ( Sheet1[rankx] ) <> 1
&& _timebigthan = 1,
MAX ( Sheet1[Maximum Percent] ) - MAX ( Sheet1[Capacity Assigned] ),
_1result
)
)
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @third_hicana ,
I hvae modified my sample, please refer to it to see if it helps you.
Measure =
VAR _mindate =
MINX ( ALLSELECTED ( Sheet1 ), Sheet1[month] )
VAR _vapacityvalue =
CALCULATE (
MAX ( Sheet1[Capacity Assigned] ),
FILTER ( ALL ( Sheet1 ), Sheet1[month] = _mindate )
)
VAR _project =
CALCULATE (
MAX ( Sheet1[project] ),
FILTER ( ALL ( Sheet1 ), Sheet1[month] = _mindate )
)
VAR _1result =
IF (
MAX ( Sheet1[project] ) = _project,
MAX ( Sheet1[Maximum Percent] ) - MAX ( Sheet1[Capacity Assigned] ),
IF (
MAX ( Sheet1[project] ) <> _project,
MAX ( Sheet1[Maximum Percent] ) - MAX ( Sheet1[Capacity Assigned] ) - _vapacityvalue,
BLANK ()
)
)
VAR _before =
CALCULATE (
MAX ( Sheet1[end date] ),
FILTER ( ALL ( Sheet1 ), Sheet1[rankx] = SELECTEDVALUE ( Sheet1[rankx] ) - 1 )
)
VAR _year =
YEAR ( _before )
VAR _month =
MONTH ( _before )
VAR _timebigthan =
IF (
MAX ( Sheet1[year] ) = _year
&& MAX ( Sheet1[month_column] ) >= _month,
1,
0
)
RETURN
IF (
MAX ( Sheet1[_countproject] ) <> 1
&& MAX ( Sheet1[rankx] ) = 1,
_1result,
IF (
MAX ( Sheet1[_countproject] ) <> 1
&& MAX ( Sheet1[rankx] ) <> 1
&& _timebigthan = 1,
MAX ( Sheet1[Maximum Percent] ) - MAX ( Sheet1[Capacity Assigned] ),
_1result
)
)
I have add other columns, please refer to my pbix file.
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @third_hicana ,
Please pay attention to the position of the parentheses.
Measure =
VAR _mindate =
MINX ( ALLSELECTED ( Sheet1 ), Sheet1[month] )
VAR _vapacityvalue =
CALCULATE (
MAX ( Sheet1[Capacity Assigned] ),
FILTER ( ALL ( Sheet1 ), Sheet1[month] = _mindate )
)
VAR _project =
CALCULATE (
MAX ( Sheet1[project] ),
FILTER ( ALL ( Sheet1 ), Sheet1[month] = _mindate )
)
VAR _1result =
IF (
MAX ( Sheet1[project] ) = _project,
MAX ( Sheet1[Maximum Percent] ) - MAX ( Sheet1[Capacity Assigned] ),
IF (
MAX ( Sheet1[project] ) <> _project,
MAX ( Sheet1[Maximum Percent] ) - MAX ( Sheet1[Capacity Assigned] ) - _vapacityvalue,
BLANK ()
)
)
VAR _before =
CALCULATE (
MAX ( Sheet1[end date] ),
FILTER ( ALL ( Sheet1 ), Sheet1[rankx] = SELECTEDVALUE ( Sheet1[rankx] ) - 1 )
)
VAR _year =
YEAR ( _before )
VAR _month =
MONTH ( _before )
VAR _timebigthan =
IF (
MAX ( Sheet1[year] ) = _year
&& MAX ( Sheet1[month_column] ) >= _month,
1,
0
)
RETURN
IF (
MAX ( Sheet1[_countproject] ) <> 1
&& MAX ( Sheet1[rankx] ) = 1,
_1result,
IF (
MAX ( Sheet1[_countproject] ) <> 1
&& MAX ( Sheet1[rankx] ) <> 1
&& _timebigthan = 1,
MAX ( Sheet1[Maximum Percent] ) - MAX ( Sheet1[Capacity Assigned] ),
_1result
)
)
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @third_hicana ,
I have created a simple sample, please refer to it to see if it helps you.
Create a measure.
Measure =
VAR _mindate =
MINX ( ALLSELECTED ( Sheet1 ), Sheet1[month] )
VAR _vapacityvalue =
CALCULATE (
MAX ( Sheet1[Capacity Assigned] ),
FILTER ( ALL ( Sheet1 ), Sheet1[month] = _mindate )
)
VAR _project =
CALCULATE (
MAX ( Sheet1[project] ),
FILTER ( ALL ( Sheet1 ), Sheet1[month] = _mindate )
)
RETURN
IF (
MAX ( Sheet1[project] ) = _project,
MAX ( Sheet1[Maximum Percent] ) - MAX ( Sheet1[Capacity Assigned] ),
IF (
MAX ( Sheet1[project] ) <> _project,
MAX ( Sheet1[Maximum Percent] ) - MAX ( Sheet1[Capacity Assigned] ) - _vapacityvalue,
BLANK ()
)
)
If I have misunderstood your meaning, please provide more details with your desired output.
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-rongtiep-msft . Thank you for this. I tried to replicate your measure in my data. However, I got some errors. Please see below.
Hi @third_hicana ,
Does that make sense? If so, kindly mark my answer as the solution to close the case please. Thanks in advance.
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello @v-rongtiep-msft
I think it did not work what I expect it to be. Supposedly, the remaining percent is 0.20 and not -.80 because other projects have already ended.
If you can go back to my sample data, Employee A has 2 overlapping months because of almost the same project start date and end date. In that case, it should add all the capacity assigned per month. If one of the project ends, the assigned capacity will be lessened in the succeeding months , that's why in my sample data in month column (row 01/07/2023) the remaining capacity is 100% because project A already ended in that date and the capacity assigned was removed and no longer added in the succeeding months.
Hi @third_hicana ,
Please pay attention to the position of the parentheses.
Measure =
VAR _mindate =
MINX ( ALLSELECTED ( Sheet1 ), Sheet1[month] )
VAR _vapacityvalue =
CALCULATE (
MAX ( Sheet1[Capacity Assigned] ),
FILTER ( ALL ( Sheet1 ), Sheet1[month] = _mindate )
)
VAR _project =
CALCULATE (
MAX ( Sheet1[project] ),
FILTER ( ALL ( Sheet1 ), Sheet1[month] = _mindate )
)
RETURN
IF (
MAX ( Sheet1[project] ) = _project,
MAX ( Sheet1[Maximum Percent] ) - MAX ( Sheet1[Capacity Assigned] ),
IF (
MAX ( Sheet1[project] ) <> _project,
MAX ( Sheet1[Maximum Percent] ) - MAX ( Sheet1[Capacity Assigned] ) - _vapacityvalue,
BLANK ()
)
)
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |