Forum Discussion

fehr_da's avatar
fehr_da
Frequent Visitor
8 years ago

running total with filter

Hi to all,

 

i have a proplem in implementing a running total using following table

 

Table structure

 

I have categories, projekt and month and costs in it.

 

My visualisation looks like this:

 

Visualization

 

Here you can see, the monthly summ of the categories are shown as bars in the bar-diagram. I can filter this view by project if I need to, which is very important for us as well.

 

What I now would like to have, is the running total of all categories displayed as a bar and in the Matrix as well and it would be great if this running total could be filtered by projects as well.

 

My first thought was to add a new categorie named "running total" and insert the sum for every month and project available in the table. 

 

Unfortunally, I have no idea, how I could achieve this.

 

Thank you a lot for any help

 

Need Help
Show and Tell

4 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    8 years ago

    Hey,

     

    unfortunately I'm not able to identify your problem through your description and the two screenshots.

     

    For this reason I recommend that you 1st go to this website, almost everything one needs for time related calculations you will find here: http://www.daxpatterns.com/time-patterns/

     

    Basically you would not create a new category, but a new measure, meaning if you have a measure called sales, you will create a measure "sales ytd" 

     

    Hope this gets you started

     

    Regards

    Tom

    • fehr_da's avatar
      fehr_da
      Frequent Visitor
      8 years ago

      I forgot to mention that I already implemented your suggestion by using following measure:

       

      DaxCumulative = CALCULATE(SUM(PROJEKTPLAN_CASHFLOW[Kosten Gesamt]);FILTER(all(PROJEKTPLAN_CASHFLOW);PROJEKTPLAN_CASHFLOW[PROJEKTPLAN_monat]<=max(PROJEKTPLAN_CASHFLOW[PROJEKTPLAN_monat])))

      here the problem is, that this measure doesn't change if I filter by projekts.

       

      Thank you very much

      • v-sihou-msft's avatar
        v-sihou-msft
        Microsoft Employee
        8 years ago

        fehr_da

         

        In this scenario, if you want to have your calculation take the filter on Projekt column, you can add ALLSELECTED() into your CALCULATE() function. 

         

        DaxCumulative =
CALCULATE (
    SUM ( PROJEKTPLAN_CASHFLOW[Kosten Gesamt] ),
    FILTER (
        ALL ( PROJEKTPLAN_CASHFLOW ),
        PROJEKTPLAN_CASHFLOW[PROJEKTPLAN_monat]
            <= MAX ( PROJEKTPLAN_CASHFLOW[PROJEKTPLAN_monat] )
    ),
    ALLSELECTED ( PROJEKTPLAN_CASHFLOW[PROJEKTPLAN_Projekt] )
)

        Regards,