Power BI percentages over time

[AC23VM]

I have a dataset like this, one row per analyst per month (ignore the numbers if they’re inaccurate, I just threw this example together!):

Month / Year / Analyst / Total Hours of Testing / Available After Shrinkage / %

March / 2025 / John Smith / 40 / 50 / 80%

April / 2025 / John Smith / 50 / 50 / 100%

March / 2025 / Jane Doe / 35 / 40 / 87.%%

April / 2025 / Jane Doe / 60 / 60 / 100%

I want to plot each analyst’s productivity percentage over time. I already have the percentages calculated in the data source, but Power BI doesn’t like the raw data in visuals. If I try to add the raw percentage data as the Y axis of a line chart, for example, every month shows ‘8’ – i.e., the number of months of data I have. I’ve also tried calculating them in DAX as measures/columns, but the results are always wrong – SUM, AVERAGEX, even a simple COUNTA all leading to inflated or incorrect results.

In a table or matrix, the row-level percentages display correctly. In line / column charts, Power BI always aggregates the values (SUM, AVERAGE, etc.), even with only one row per analyst/month. This produces inflated or identical percentages, including for months with no data.

Charts work correctly for event-level data because aggregation sums numerator and denominator naturally. Row-level percentages don’t behave the same way. But as far as I know, Power BI can’t natively plot exact row-level percentages over time in standard chart visuals. Only tables/matrices seem to display them correctly without aggregation. Has anyone found a way around this?

The essay question is - How can I show John Smith’s productivity percentages over time in a visual?

Before anyone asks, unfortunately I can’t upload an example data source.

[Greg_Deckler · Accepted solution]

@AC23VM Sounds like you need to put Analyst in the Legend or as a small multiple. The other option would be to create one chart per analyst and use the Filters pane to filter it to just a single analyst.

[AmiraBedh · Accepted solution]

Hello !

Thank you for posting on MS Fabric community.

You need to use a measure and not a calc column.

Productivity % = DIVIDE( SUM([Total Hours of Testing]), SUM([Available After Shrinkage]) )

since the measure sums numerator and denominator first, so each analyst×month point shows the correct % and scales correctly when you change filters/levels.

You will find the solution in the attached pbix

[Ritaf1983 · Accepted solution]

Hi @AC23VM 

It is correct that chart visuals in Power BI do not display row-level values but aggregated values. This is not a limitation of the tool, but a core principle of data visualization. Charts are not designed to show raw numbers, but to illustrate relationships between them. Since the human brain cannot effectively interpret relationships only by reading numbers, charts use geometric shapes to make them visible.

In every chart, each dimension (categorical axis, legend, etc.) gives context to the measure on the value axis. That means the measure is always sliced by these dimensions.
For example, if you create a bar chart showing the average percentage by analyst, the aggregation is AVG, not SUM, because percentages from multiple months cannot be added together. See the first example chart.

If you also want to slice by time (months) and analysts, the context will come from the X axis (month) and the legend (analysts). In this case a line chart is suitable, as shown in the second example.

From a UX perspective, if all measures are analyzed by analyst, it can be helpful to combine them in a single table enhanced with mini-charts or sparklines. This way you can show both the exact numbers and the trends where needed. See the third example. Order the measures by importance and use neutral colors (gray) for less critical values, with stronger colors (blue) for the most important ones. If there are exceptional percentages, you can also add alerts (for example, a red circle marker).

The pbix with the examples is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[AmiraBedh · Accepted solution]

Hello !

Thank you for posting on MS Fabric community.

You need to use a measure and not a calc column.

Productivity % = DIVIDE( SUM([Total Hours of Testing]), SUM([Available After Shrinkage]) )

since the measure sums numerator and denominator first, so each analyst×month point shows the correct % and scales correctly when you change filters/levels.

You will find the solution in the attached pbix

[Greg_Deckler · Accepted solution]

@AC23VM Sounds like you need to put Analyst in the Legend or as a small multiple. The other option would be to create one chart per analyst and use the Filters pane to filter it to just a single analyst.