Forum Discussion
Cumulative % by month
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!