Issue with Running Total Calculation in Power BI with Filters

[Praj8050]

Dear Power BI Community,

I am reaching out for assistance with an issue I am facing regarding a running total calculation in Power BI. The formula works correctly when no filters are applied but produces incorrect results when filters are used. Below, I have provided all the details regarding my issue and the DAX formula I used.

Issue Description

I am calculating a running total for the Units column based on Category and Segment. The running total works perfectly when the data is unfiltered. However, when filters are applied (e.g., by country, order_date, or other fields), the running total does not behave as expected and returns incorrect values.

DAX Formula Used

Here is the formula I used to calculate the running total:

Running Total Units =

CALCULATE( SUM(df[Units]), FILTER( ALLEXCEPT(df, df[category], df[country], df[segment], df[order_id], df[week_date], df[sub_category]), df[category_sort_order] <= MAX(df[category_sort_order]) ) )

 

1. category_sort_order:

   • I have a calculated column df[category_sort_order] that assigns a numeric order to each category. Below is a sample:Category Category_sort_order
     
     
     

2. Expected Behavior:

   • The running total should compute the cumulative sum of Units while respecting both the Category and Segment context.
   • For example, if the data is filtered by week_date = '10/1/2024'or country = "USA", the running total should only consider the relevant rows while maintaining the cumulative logic for the visible categories.
     
     

3. Actual Behavior:

   • When filters are applied, the running total values become incorrect. It seems the formula does not respect the filtered context properly.

[v-nmadadi-msft]

Hi @Praj8050 ,

We are following up once again regarding your query. 

If the issue has been resolved, we kindly request you to share the resolution or key insights here to help others in the community. If we don’t hear back, we’ll go ahead and close this thread.

Should you need further assistance in the future, we encourage you to reach out via the Microsoft Fabric Community Forum and create a new thread or reopen this thread. We’ll be happy to help.

Thank you for your understanding and participation.

[v-nmadadi-msft]

Hi @Praj8050 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[Praj8050]

Hi Team

Issue is not yet resolved 

[v-nmadadi-msft · Accepted solution]

Hi @Praj8050 ,
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Please show the expected outcome based on the sample data you provided.
Thanks and regards

[v-nmadadi-msft]

Hi @Praj8050 ,

Any update regarding your issue?
 
Thanks and regards

[v-nmadadi-msft]

Hi @Praj8050 

The issue is most likely caused by running total measure dax measure does not respect the filtered context when filters are applied.

https://learn.microsoft.com/en-us/dax/allexcept-function-dax

As per the above documentation AllExcept will remove all context filters except that have been applied in the specific columns.

Try using Allselected which will keeps filters from the visual but removes external filters

https://learn.microsoft.com/en-us/dax/allselected-function-dax

Thanks and regards

[danextian]

Hi @Praj8050 

Are you supposed to based the running total on a sort column or is it the running total based on dates?

[Praj8050]

Running Total based on Category and Segment. Category in column and Segment in Rows 

[danextian]

I would use a separate dimension for  category and apply ALL to its own table but that's just what I think will work. Can you please post a workable sample data (not an image) and certainly not in the format as your expected output.