DAX for Remaining Percent Left and Reflect in Bar Graph

[third_hicana]

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%

[v-rongtiep-msft]

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.

[third_hicana]

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

[v-rongtiep-msft · Accepted 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.

[v-rongtiep-msft]

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.

[third_hicana]

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. 

[v-rongtiep-msft]

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.

[third_hicana]

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. 

[v-rongtiep-msft]

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.