Forum Discussion

Praj8050's avatar
Praj8050
Icon for Helper I rankHelper I
1 year ago
Solved

Issue with Running Total Calculation in Power BI with Filters

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.
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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

9 Replies

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    1 year ago

    Hi Praj8050 

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

    • Praj8050's avatar
      Praj8050
      Icon for Helper I rankHelper I
      1 year ago

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

       

      • danextian's avatar
        danextian
        Icon for Super User rankSuper User
        1 year ago

        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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      Praj8050
      Icon for Helper I rankHelper I
      1 year ago

      Hi Team

       

      Issue is not yet resolved 

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.