cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
third_hicana
Helper III
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. 

 

 

 

third_hicana_0-1664862691927.png

 

third_hicana_0-1664868125881.png

 

 



Month   Assigned Resource Project    Start Date End Date Maximum Percent Capacity Assigned Remaining Percent

01/07/2022Employee AProject A22/07/202231/07/2023100%40%60%
01/08/2022Employee AProject A22/07/202231/07/2023100%40%60%
01/09/2022Employee AProject A22/07/202231/07/2023100%40%60%
01/10/2022Employee AProject A22/07/202231/07/2023100%40%60%
01/11/2022Employee AProject A22/07/202231/07/2023100%40%60%
01/12/2022Employee AProject A22/07/202231/07/2023100%40%60%
01/01/2023Employee AProject A22/07/202231/07/2023100%40%60%
01/02/2023Employee AProject A22/07/202231/07/2023100%40%60%
01/03/2023Employee AProject A22/07/202231/07/2023100%40%60%
01/04/2023Employee AProject A22/07/202231/07/2023100%40%60%
01/05/2023Employee AProject A22/07/202231/07/2023100%40%60%
01/06/2023Employee AProject A22/07/202231/07/2023100%40%60%
01/07/2023Employee AProject A22/07/202231/07/2023100%40%60%
01/09/2022Employee AProject B01/09/202201/09/2023100%100%-40%
01/10/2022Employee AProject B01/09/202201/09/2023100%100%-40%
01/11/2022Employee AProject B01/09/202201/09/2023100%100%-40%
01/12/2022Employee AProject B01/09/202201/09/2023100%100%-40%
01/01/2023Employee AProject B01/09/202201/09/2023100%100%-40%
01/02/2023Employee AProject B01/09/202201/09/2023100%100%-40%
01/03/2023Employee AProject B01/09/202201/09/2023100%100%-40%
01/04/2023Employee AProject B01/09/202201/09/2023100%100%-40%
01/05/2023Employee AProject B01/09/202201/09/2023100%100%-40%
01/06/2023Employee AProject B01/09/202201/09/2023100%100%-40%
01/07/2023Employee AProject B01/09/202201/09/2023100%100%0%
01/08/2023Employee AProject B01/09/202201/09/2023100%100%0%
01/09/2023Employee AProject B01/09/202201/09/2023100%100%0%
1 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.

 

View solution in original post

8 REPLIES 8
v-polly-msft
Community Support
Community Support

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 @v-polly-msft I have a one error.  The end of the input was reached.

third_hicana_0-1664963263424.png

 

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-polly-msft
Community Support
Community Support

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

 

vpollymsft_1-1664942937882.png

 

 

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-polly-msft . Thank you for this. I tried to replicate your measure in my data. However, I got some errors. Please see below. 

third_hicana_3-1664949680987.png

 



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-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.

third_hicana_0-1664953977602.png


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.

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.