Create a DAX check based on the sum of sorted values for a given period, and group

[kasiaPI]

Hello,

I'm struggling with writing a DAX measure that will check if the sum of the ticket efforts (that is already a calculated by a measure 'Effort calc') for a specified group will suit this group capacity (as well measure "Capacity calc'). If don't then it should be commented ora color marked.

In addition the Ticket effort summary must be done for a given Release period where tickets needs to be ordered by a priority assigned to them. 

For now I've created something like this:

CheckCapacity = 

IF (

    SUMX (

        VALUES('YourTable'[Release], 'YourTable'[Group]),

        CALCULATE (

            SUMX (

                VALUES('YourTable'),

                'YourTable'[Effort] * 'YourTable'[Priority]

            )

        )

    ) <= SUMX (

        VALUES('YourTable'[Release], 'YourTable'[Group]),

        CALCULATE (

            MAX('YourTable'[Capacity])

        )

    ),

    "Fits",

    "Doesn't Fit"

)

but doesnt work and not sure if multiplying the effort by priority is a good idea, because efforts might be any value where for priority we have a specified ranges for a release.

Thank you fo any suggestion with this complicated logic 🙂

[Anonymous]

Hi, @kasiaPI 

Since you have not provided any data, I cannot understand what effect you are trying to achieve at the moment, you can either share a pbix file that doesn't contain sensitive data or post a full description that describes your problem, including information about the datasheets, the measurements, the expected effect, and the logic to achieve the effect.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[kasiaPI]

Hi,

Unfortunately I can't share any file with you as I'm working on a company's sensitive data. Additionally I can't modify anything in the tables because I'm working on the shared dataset so I only can manipulate with DAX.

In this problem I'm playing with two tables "Capacities' and 'Efforts' they're connected with the relationship througt the other table 'Releases'  by field 'release' (One to many from 'Release' table to the two other tables).

I need to do a calculation that will show a 'red line' where the sum of efforts from the 'Efforts' table (this effort field is a measure) ordered by priority that is given for each of the item from the table will meet the sum of capacity (from a 'Capacity' table that is as well a measure' in a given release, and for given Group.

So e.g there is a grup that have some items with efforts and priorities assigned to them, now I would like to see how many items base on the priority order (asc) group will be able to realize with given efforts to not exceed the capacity.

This is how I need to see the result of the whole operation:

Item ID     effort      priority      check                  capacity in release 1 = 60

a               10              1              fit

b               20              2              fit 

c                25              3              fit

d               15               4              doesn't fit

So we should know what are the Items with a higher priority that we can proceed to not exceed the capacity.

I hope it helps a bit more to understand the issue.

Many thanks for your support!

[123abc]

Here's a revised version of your DAX measure:

CheckCapacity =
VAR TotalEffort =
SUMX (
FILTER (
'YourTable',
'YourTable'[Release] = SELECTEDVALUE('YourTable'[Release]) &&
'YourTable'[Group] = SELECTEDVALUE('YourTable'[Group])
),
'YourTable'[Effort]
)

VAR GroupCapacity =
MAXX (
FILTER (
'YourTable',
'YourTable'[Release] = SELECTEDVALUE('YourTable'[Release]) &&
'YourTable'[Group] = SELECTEDVALUE('YourTable'[Group])
),
'YourTable'[Capacity]
)

RETURN
IF (
TotalEffort <= GroupCapacity,
"Fits",
"Doesn't Fit"
)

Here's how the measure works:

• TotalEffort: Calculates the total effort for the specified group within the selected release period.
• GroupCapacity: Retrieves the capacity for the specified group within the selected release period.
• The measure then compares TotalEffort with GroupCapacity and returns "Fits" if the total effort is within the capacity, otherwise returns "Doesn't Fit".

Make sure you adjust column and table names ('YourTable', 'Release', 'Group', 'Effort', 'Capacity') according to your data model. This measure should help you achieve the desired functionality.