topic Re: Cumulative % by month in Custom Visuals Development Discussion

Cumulative % by month

deaconb — Fri, 24 Jan 2025 17:37:00 GMT

I have some training data that I need to chart cumulative completion % per person (toward 100%) . I have two primary measures I'm using to calculate a person's training completion to date: Training Points Earned and % Cumulative Training.

I'm getting the "points" earned by this measure (this normalizes 1 training = 1 point):

Training Points Earned = 
CALCULATE(COUNTROWS('Data'), 
FILTER('Data', [Difference from Previous Training] > 0 || ISBLANK([Difference from Previous Training])))

Note: This measure leverages another measure to assess if a training receives a point or not (there are occurrences where a training doesn't receive a point).

I'm then getting the cumulative training completed to date from this measure (my denomentaror is coming from a source where total expected points per user are stored):

% Cumulative Training =
CALCULATE(SUMX('Data', DIVIDE([Training Points Earned], MAX('Course Codes'[Total Workgroup Points]))),
    FILTER (
        ALLEXCEPT('Data', 'Data'[USERNAME]),
        'Data'[RESPONSEID] <= MAX( 'Data'[RESPONSEID])
    )
)

Note: People can update their record after generation, therefore, I'm using the RESPONSEID versus a Date field to keep the data chronological, leveraging the IDs numerical order as issued by the system.

Both of my measures are working as expected, as evidenced in my sample data below.

Here is where I'm struggling:

When I input my % Cumulative Training measure onto a line graph, it's summing all previous rows giving me well over the 100% expected maximum. Right now my data is charting by a week number instead of month name, but the issue remains the same.
- Changing my axis limits wouldn't help resolve the issue because the data itself isn't presenting how I want it to.

I believe I need to obtain the maximum value of a given month per user. I've tried to write a measure to do this, however, I can't get the data to return properly. I can only get the maximum % per user, and not their incremental maximums per month.

Here is my sample data: Sample Data. I've highlighted the rows I think I need to capture to properly chart the data. I am stuck with how to proceed and I'd appreciate any gudiance anyone can offer!

Re: Cumulative % by month

lbendlin — Sun, 26 Jan 2025 01:45:18 GMT

You have disabled download and copy/paste. Please remove at least one of the restrictions.

Re: Cumulative % by month

deaconb — Sun, 26 Jan 2025 15:19:22 GMT

Done

Re: Cumulative % by month

lbendlin — Mon, 27 Jan 2025 02:28:56 GMT

What's the contents of the 'Course Codes' table?

Re: Cumulative % by month

deaconb — Mon, 27 Jan 2025 13:58:34 GMT

It's the required training - not really relevant to the issue at hand except to understand where a denominator is coming from.

Re: Cumulative % by month

lbendlin — Mon, 27 Jan 2025 14:58:26 GMT

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

Re: Cumulative % by month

deaconb — Tue, 28 Jan 2025 22:57:17 GMT

I'm looking to chart cumulative training data without the underlying data adding up the way it is now when charted (see original post). I think the way to do this is to obtain the max value of a given month and the latest overall value (highlighted rows in my sample data).

I'd like to understand if my thought process is accurate for how to chart my data so it appears something like this, where each user's cumulative completion is charting toward 100%:

And if my thought process is accurate, how to get that data via a measure or caluclated column as that's where I'm stuck. Or if there's a better solution for what I'm trying to do, I'm open to that!