cancel
Showing results for
Did you mean:
Helper III

## DAX for Remaining Percent Left and Reflect in Bar Graph

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%
1 ACCEPTED SOLUTION
Community Support

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.

8 REPLIES 8
Community Support

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

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.

Helper III

hI @v-polly-msft I have a one error.  The end of the input was reached.

Community Support

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.

Community Support

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 ()
)
)
``````

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.

Helper III

Hi @v-polly-msft . Thank you for this. I tried to replicate your measure in my data. However, I got some errors. Please see below.

Community Support

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.

Helper III

Hello @v-polly-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.

Community Support

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.

Announcements