Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Filtering Running Total

Hi there,

I'm facing some trouble here when trying to insert a filtering condition for a Running Total of cumulative sales.

Althought the running total without filter runs perfectly, when I try to put a filter into the sentence it stops showing a "growth" line and starts showing a line that goes up and down

 

My Running Total, one which is running perfectly is:

 
 
M Sales SUM running total over Date =
CALCULATE(
SUM('Sales'[Value]);
FILTER( ALLSELECTED('Sales'); 'Sales'[Date] <= MAX ( 'Sales'[Date] ) ))
 
 
 
My objective now is being able to run a DAX like this, and still showing in my chart a running total growing line:
 
 

M Filtered Sales SUM running total over Date =
CALCULATE(
SUM('Sales'[Value]);
FILTER('Sales';'Sales'[Country]="Brazil");
FILTER( ALLSELECTED('Sales'); 'Sales'[Date] <= MAX ( 'Sales'[Date] ) ))

 

 

 

 

Can you help me ?

Need Help
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    Try this measure instead.  Filtering the whole Sales table is affected your filters more than you intended.  Note that I changed your ALLSELECTED() part too.  If this doesn't work, you can change it back to ALLSELECTED('Sales').  I don't know your model, so could be either way.

     

    M Filtered Sales SUM running total over Date =
    CALCULATE(
    SUM('Sales'[Value]);
    'Sales'[Country]="Brazil";
    FILTER( ALLSELECTED('Sales'[Date]); 'Sales'[Date] <= MAX ( 'Sales'[Date] ) ))

     

    If this solution works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

     

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Anonymous , Try like


    CALCULATE(
    SUM('Sales'[Value]);
    FILTER( ALLSELECTED('Sales'); 'Sales'[Date] <= MAX ( 'Sales'[Date] ) && 'Sales'[Country]="Brazil"))

    • parry2k's avatar
      parry2k
      Icon for Super User rankSuper User
      6 years ago

      Anonymous As a best practice, add date dimension in your model and use it for and time intelligence calculations. There are many posts on how to add date dimension and below is the link to a few. Once the date dimension is added, mark it as a date table on table tools.

      https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
      https://radacad.com/create-a-date-dimension-in-power-bi-in-4-steps-step-1-calendar-columns

       

      Add following measure and it will work more efficient on large datasets

       

      SUM('Sales'[Value]);
FILTER( ALLSELECTED('CalendarTable'[Date]); 'CalendarTable'[Date] <= MAX ( 'CalendarTable'[Date] ) ),
'Sales'[Country]="Brazil"
)

      Let's try to follow the best practice for scalable solutions. I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    Try this measure instead.  Filtering the whole Sales table is affected your filters more than you intended.  Note that I changed your ALLSELECTED() part too.  If this doesn't work, you can change it back to ALLSELECTED('Sales').  I don't know your model, so could be either way.

     

    M Filtered Sales SUM running total over Date =
    CALCULATE(
    SUM('Sales'[Value]);
    'Sales'[Country]="Brazil";
    FILTER( ALLSELECTED('Sales'[Date]); 'Sales'[Date] <= MAX ( 'Sales'[Date] ) ))

     

    If this solution works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

     

  • Icey's avatar
    Icey
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    Is this problem solved?

     

     

    Best Regards,

    Icey