DAX Query- Sum cumulative percentage

[mcpowerbi2025]

Hi,

I have created a measure in Power BI to work out a percentage. I'd like to be able to sum that percentage up so it shows the cumulative percentage over time but haven't been able to find a solution. Can anyone help? I have created a table below as an example of what I'm trying to do.

Month	Monthly %	Cumulative %
April	2%	2%
May	1%	3%
June	3%	6%
July	4%	10%

[Anonymous]

Hi @mcpowerbi2025 ， hello lbendlin  and DataNinja777, thank you for your prompt reply!

Is there any progress on this issue?

If you find any answer is helpful to you, please remember to accept it.

It will help others who meet the similar question in this forum.

Thank you for your understanding.

[mcpowerbi2025]

Thanks. I managed to get the running total working. Just might need to do a bit of work on the data to get it working with slicers etc.

[lbendlin]

for that you would need to use materialized measures as mentioned earlier.

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

[mcpowerbi2025]

Here is an example of the data I'm using.

The calculation for the monthly % is

count of Ten End in the that month

divided by

sum of Properties in scheme in the that month.

I'm then trying to accumulate the monthly percentage to get the running total.

Hope this is all you need. Thanks again for your help.

https://file.io/gteUv04kc0sS 

[lbendlin]

Based on this data model

Note that while this is what you are asking for it does not make much mathematical sense.

[DataNinja777]

Hi @mcpowerbi2025 ,

Adding percentages directly can be problematic because percentages are relative values, and their meaning depends on the context of their denominators. Summing percentages without considering the base values they are derived from can lead to misleading results. For example, if we calculate percentages as 1/100 (1%) and 10/1000 (1%), adding these percentages gives 2%, which completely ignores the significant difference in their base values (100 and 1000). This demonstrates that the sum of percentages may not provide any meaningful insight unless the underlying data is accounted for.

To calculate cumulative percentages accurately, it’s essential to use the absolute values from which the percentages are derived. Instead of summing the percentages, you need to calculate cumulative totals for both the numerator (e.g., sales, counts) and the denominator (e.g., total sales, total counts). The cumulative percentage is then calculated by dividing the cumulative numerator by the cumulative denominator.

In Power BI, you can achieve this with a DAX formula. The cumulative numerator is calculated as the sum of numerator values up to the current row, while the cumulative denominator is calculated in a similar way. The formula to calculate the cumulative percentage looks like this:

Cumulative % = 
DIVIDE(
    CALCULATE(SUM('Table'[Numerator]), FILTER(ALL('Table'[Month]), 'Table'[Month] <= MAX('Table'[Month]))),
    CALCULATE(SUM('Table'[Denominator]), FILTER(ALL('Table'[Month]), 'Table'[Month] <= MAX('Table'[Month])))
)

This approach ensures that the cumulative percentage reflects the true proportion over time. For example, if the numerator values for April and May are 10 and 20, and the denominators are 100 and 200, the cumulative numerator would be 30, and the cumulative denominator would be 300. The cumulative percentage, calculated as 30/300, results in 10%, maintaining the accuracy and relevance of the data. This method avoids the common pitfall of summing relative values, ensuring that the cumulative percentage provides meaningful insights.

Best regards,

[mcpowerbi2025]

Thanks for your help but this doesn't work for me.

Sorry, I probably should have explained better. To work the percentage cumulative, suming the denominator won't work as it'll be too high if it does.

[lbendlin]

Please check out the Quick Measure option - use the "Running Total"  template.

[mcpowerbi2025]

Thanks for your help but this hasn't seemed to work either.

[lbendlin]

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

[mcpowerbi2025]

Thanks. Here is the dummy data. What I'm expecting is in the highlighted column.

https://file.io/jr7YaZZRtmXJ 

[lbendlin]

Since your Prop value is different per month you need to materialize the monthly percentage - for example as a column rather than a measure.  

Even the running total could be done that way, but a quick measure would do too.