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!

amitchandak's avatar
amitchandak
Super User
5 years ago

LauraAshburn , is in the second screen shot date is coming from date table ?

If yes then you need to use date table here in this measure

 

Running Billings RBC =
CALCULATE(
SUMX(FILTER(Budget,Budget[BudgetSort] = 5),Budget[Budget]),
FILTER(
ALLSELECTED('Date'[Date]),
ISONORAFTER('Date'[Date], MAX('Date'[Date]), DESC)
)
)

 

Else share the calculation of all meausres .

Or Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

  • LauraAshburn's avatar
    LauraAshburn
    Helper I
    5 years ago

    I have two measures actually. The first works after some modifying.

    Running Billings RBC =
    CALCULATE(
    SUM(billing_activities[Actual Billings RBC]),
        FILTER(
            ALLSELECTED('billing_activities'[Date]),
            ISONORAFTER('billing_activities'[Date], MAX('billing_activities'[Date]), DESC)
    ))
     
    The second is giving me fits. 
    Running Budget RBC =
    CALCULATE(
        SUMX(FILTER(Budget,Budget[BudgetSort] = 5),Budget[Budget]),
        FILTER(
            ALLSELECTED('billing_activities'[Date]),
            ISONORAFTER(billing_activities[Date], MAX('billing_activities'[Date]), DESC)
        )
    )
    Now I am using the same table having the dates. 
    The Billings and Budget tables are joined by date.
    In the Budget table , BudgetSort in an integer to identify the product. I created a column in the Billings table using a switch statement so both tables now have date and BudgetSort to join on. I don't know where in the calculation to filter for it or join them.
    • LauraAshburn's avatar
      LauraAshburn
      Helper I
      5 years ago

      I haven't ever posted before. Where to up upload the .pbix? That way you can at lease see the measures, right?

      • amitchandak's avatar
        amitchandak
        Super User
        5 years ago

        LauraAshburn , You can upload to one drive or dropbox and share the link

  • LauraAshburn's avatar
    LauraAshburn
    Helper I
    5 years ago

    I haven't ever posted before. Where to up upload the .pbix? That way you can at lease see the measures, right?