Forum Discussion

RvdHeijden's avatar
RvdHeijden
Post Prodigy
7 years ago
Solved

HELP !! simple calculation ?!

Goodday, I need a calculation which sums up the total of a certain column. For example the column 'Aantal HP gerealiseerd' has a number of HP's per week but i need a column that shows the cumulat...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    RvdHeijden,

     

    Modeling -> New measure, and copy the code: 
    Cumulatieve HP gerealiseerd =
    CALCULATE ([Totaal aantal HP];
    FILTER(
    ALL(Forecast);
    'Forecast'[Week]<=MAX(Forecast[Week])
    )

     

    It will work as a line or bar graph. Put a Date dimension on the Axis and your newly created measure to Values.
    For me it looks like this:

     

BobBI's avatar
BobBI
Resolver III
7 years ago

Hi ,

 

There several method to calculate cumulative total , however considering your sample data , this DAX will is simple way to achieve the desired output.

 

Your Data (table = Rve Data)

Col1   Col2  Number

2018   35       186

2018   36       232

2018   37       309

2018   38       150

2018   39       215

 

First of all i created a measure called 'Total Number' = sum('Rvd Data'[number])

Second Measure called Cumulative number ( Dax below)

Cumulative number = CALCULATE ([Total number],
                                FILTER(
                                        ALL('Rvd Date'),
                                        'Rvd Date'[Col2]<=MAX('Rvd Date'[Col2])
                                      )
                                 )

 

Hope this helps,

Cheers

SS

 

  • RvdHeijden's avatar
    RvdHeijden
    Post Prodigy
    7 years ago

    BobBI thanks for the reply but it doesn't work just yet.

    Ive added the measure and the formula but it just returns the same value on all rows.

     

    The only thing i can see is that you have 2 colums for the Year-Week (Col1 and Col2) and i have one column (Week)

     

    Totaal aantal HP = SUM(Forecast[Aantal HP gerealiseerd])

     

    Cumulatieve HP gerealiseerd =
    CALCULATE ([Totaal aantal HP];
    FILTER(
    ALL(Forecast);
    'Forecast'[Week]<=MAX(Forecast[Week])
    )
    )

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi RvdHeijden,

       

      try to create a measure out of the Cumulative HP gerealiseerd calculation instead of a calculated column.

       

      Regards,

      Barna 

      • RvdHeijden's avatar
        RvdHeijden
        Post Prodigy
        7 years ago

        Anonymous 

         

        How ? 

         

        Besides i need this data for a bar or a line graph so does that still work if i use a measure ?

    • BobBI's avatar
      BobBI
      Resolver III
      7 years ago

      hi Rvd,

       

      can you split 2018-35  by  '-' ?  because '2018-35' is become a text as it contains hiphen (-) sign. if you split them or use any month or date fields with numeric or date data type , then this would work.

       

      forcast week number should be numeric . and check the values in table visual as it would summarise the amount value by week.

      calcuated column wouldn't help.

       

      Thanks,

      SS

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hello Make sure your Week Column you are using, comes from a Date Defined Table, in other words there must be a Column with a Date Value and this week must be a derived Column of that Date (then use Date instead of Week in the Formula)

       

      To make sure you are using a Date Defined table, right click the dataset and select Define as Date Table) in the fields section.