Forum Discussion

mukhan169's avatar
mukhan169
Helper III
7 years ago
Solved

Another Running Total Question

I apologize but searching through the forum I cant find the answer to the following question. May be my concept is not very clear. But thats how it goes, I have following table.

The measure is simple

Count without zero = COUNT(Claims[ID])
And trying to do the running total.
 
RunningTotal =
CALCULATE([Count without zero]|
FILTER(
ALLSELECTED(Claims)|
Claims[BMonthYear]<=MAX(Claims[BMonthYear]))
)
As you can see Its not doing the running total line by line. Its doing the total sum . Can someone please help me its seems very easy but I am pulling my hair on this.
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    hi, mukhan169 

    Just add a conditional in your formula:

    &&Claims[MIS]<=MAX(Claims[MIS])

     

    Measure = CALCULATE([Count without zero],
FILTER(
ALLSELECTED(Claims),
Claims[MonthYear]<=MAX(Claims[MonthYear])
&&Claims[MIS]<=MAX(Claims[MIS])))

    Result:

     

    Best Regards,

    Lin

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    So your calculation is only looking at the MonthYear column when deciding what to sum. Try making a new Column that is MonthYear and MIS(assuming they always increase and no repeats). Then replace MonthYear with the new column in your running total.

    • mukhan169's avatar
      mukhan169
      Helper III
      7 years ago

      Month year is already a seprate column and you are correct MIS(Month in service ) doesnt repeat for the same Month Year.

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    7 years ago

    hi, mukhan169 

    Just add a conditional in your formula:

    &&Claims[MIS]<=MAX(Claims[MIS])

     

    Measure = CALCULATE([Count without zero],
FILTER(
ALLSELECTED(Claims),
Claims[MonthYear]<=MAX(Claims[MonthYear])
&&Claims[MIS]<=MAX(Claims[MIS])))

    Result:

     

    Best Regards,

    Lin

    • mukhan169's avatar
      mukhan169
      Helper III
      7 years ago

      v-lili6-msft 

      Thank you very much. I had to change the formula so it only calculates running total for each month Year.

       

      RunningTotal =
      CALCULATE([Count without zero]|
      FILTER(
      ALLSELECTED(Claims)|
      Claims[BMonthYear]=MAX(Claims[BMonthYear])
      && Claims[MIS]<=MAX(Claims[MIS])
      )
      )