Forum Discussion

LauraAshburn's avatar
LauraAshburn
Helper I
5 years ago
Solved

Running Totals using Multiple Filters

I have read many articles for two days on how to get a running total and have not found the solution I need.  I'm trying to calculate the budget value from one table and filter the dates relevant to...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi LauraAshburn ,

     

    As the relationship between "billing activities" and "Budget" is many to many,when you directly add the data to the table visual,it may duplicate the same value,the best way is to create a measure to get the value from budget you need :

    _Budget = CALCULATE(SUM(Budget[Budget]),FILTER('Budget','Budget'[BudgetDate]=SELECTEDVALUE(billing_activities[Date])),'Budget'[BudgetSort]=5)

    And you will see:

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!

aj1973's avatar
aj1973
Community Champion
5 years ago

Hi LauraAshburn 

In my understanding you are using different date tables for the same purpose, if so that's not the right way espacially when using Time Intelligence formulas like MTD

You need to add a calendar date to your model.

  

  • LauraAshburn's avatar
    LauraAshburn
    Helper I
    5 years ago

    I have a calendar date in my model.  I have my billing table and budget table joined by date. The second filter in my expression is working. What I'd like to do is add another filter in the second filter to filter by product. I would think it would be simular to  SUMX(FILTER(Budget,Budget[BudgetSort] = 5),Budget[Budget]), but use the Budget Sort column in the billings table.

    • v-kelly-msft's avatar
      v-kelly-msft
      Community Support
      5 years ago

      Hi LauraAshburn ,

       

      There seems no error in your dax expression,if you could provide some sample data(with the key value,such as budgetsort,budget inside),I would test and find a solution.

       

      Best Regards,
      Kelly

      Did I answer your question? Mark my post as a solution!

      • LauraAshburn's avatar
        LauraAshburn
        Helper I
        5 years ago

        Hi Kelly. 

        Here is my model. The inactive relationship is the BudgetSort which identifies my product group.

        Here are the two tables separately that  would like to join. Billing on the left, Budget on the right.

        When I add Budget to Billing, the weekend numbers don’t tie with the correct date

        Here is the DAX for the measure for Running Budget ABC