If Count equals the sum of a column from a related table return a status.

[Anonymous]

Hello,

I am trying to find a way to return a completion status when the count of completed training activities equals the sum of required training activities.

The data has a category called training names, and a subcategory called activity names.
The data has a column to show if the activity name is "completed" or not completed".

Not all activity names are required for a category to be considered completed. The customer does not have specific activty names which are required, so they are requiring a specific number of activities to be completed in order for the category to be completed.

To show the number of required activies for each training category, I created a table that list the number of required activity names per training training names.

The customer wants to see the information in a matrix. The matrix should show The traning name as "completed" if the amount of required activities is completed by employee and the activites that are completed should show "completed". When the activities are more than 0 but less than the required amount, the training name should show as "In-Progress", but the activity names should show either "completed" or "not started".

Data Example:

Data Example

Requirements Table Example:

Requirements Table Example    

Desired Output Example:

I've tried , If count of activity name equals the sum required activities, then completed, else if count of activity name is less than the amount of required activities but greater than 0, then In Progress, else not started. The output shows the category is completed but when drilling down to the activity they say in progress or not started instead of completed or not started.

I also tried this:
Category Count= IF([Completed]>=SUM('Training Type'[Category Requirment]), CALCULATE(DISTINCTCOUNT(Academy Training Status'[Category Name]), ' Academy Training Status'[Status]="Completed"),0)

(sorry for the typos)

Help?

[MFelix]

Hi @Anonymous ,

First of all add a column to ID to the Requirements table (for sorting purposes).

I assume you have a relationship between both table: now add the following two measures:

Status_measure = IF(HASONEVALUE(Data[Activity Name]);SELECTEDVALUE(Data[Status]);BLANK())

Total Status =
VAR Count_Activities =
    CALCULATE ( COUNT ( Data[Status] ); Data[Status] = "Completed" )
VAR Required_Activities =
    SUM ( Requirements[Required Activities] )
RETURN
    IF (
        HASONEVALUE ( Data[Activity Name] );
        BLANK ();
        IF (
            MAX ( Data[Status] ) <> BLANK ();
            SWITCH (
                TRUE ();
                Count_Activities >= Required_Activities; "Completed";
                Count_Activities = 0; "Not Started";
                "In Progress"
            );
            BLANK ()
        )
    )

Final result in attach file.