Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Cumulative measure without date

I need help with creating a couple of cumulative measures 🙂

 

I have the following data in a table

 

 

 

 

 

 

 

 

I want to create a cumulative measure for the TotalCount and TotalRun but ONLY for those that have the week number in the title...so it should look something like this:

 

 

 

 

What would be the correct measure to use?

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Figured it out - the solution is:

    1) Add an IF statement for the week number

    WeekRank = IF('Test Plan Rollup2'[TestSuite.TitleLevel2] = "Week 1", 1, IF('Test Plan Rollup2'[TestSuite.TitleLevel2] = "Week 2", 2, IF('Test Plan Rollup2'[TestSuite.TitleLevel2] = "Week 3", 3, IF('Test Plan Rollup2'[TestSuite.TitleLevel2] = "Week 4", 4, IF('Test Plan Rollup2'[TestSuite.TitleLevel2] = "Week 5", 5, IF('Test Plan Rollup2'[TestSuite.TitleLevel2] = "Week 6", 6, 0))))))
     

    2) Create 2 x measures - example being:

    RunCumulative =

    CALCULATE(
        SUM('Rollup'[RunCount]),
        FILTER(ALLSELECTED('Rollup'),
        'Rollup'[WeekRank] <= MAX('Rollup'[WeekRank])
        )
    )
     

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Figured it out - the solution is:

    1) Add an IF statement for the week number

    WeekRank = IF('Test Plan Rollup2'[TestSuite.TitleLevel2] = "Week 1", 1, IF('Test Plan Rollup2'[TestSuite.TitleLevel2] = "Week 2", 2, IF('Test Plan Rollup2'[TestSuite.TitleLevel2] = "Week 3", 3, IF('Test Plan Rollup2'[TestSuite.TitleLevel2] = "Week 4", 4, IF('Test Plan Rollup2'[TestSuite.TitleLevel2] = "Week 5", 5, IF('Test Plan Rollup2'[TestSuite.TitleLevel2] = "Week 6", 6, 0))))))
     

    2) Create 2 x measures - example being:

    RunCumulative =

    CALCULATE(
        SUM('Rollup'[RunCount]),
        FILTER(ALLSELECTED('Rollup'),
        'Rollup'[WeekRank] <= MAX('Rollup'[WeekRank])
        )
    )
     
  • Arul's avatar
    Arul
    Icon for Super User rankSuper User
    3 years ago

    Anonymous ,
    Have tried the below formulas in your measures?

    CountCumulative = 
CALCULATE(
    SUM('Sample'[Totalcount]),
    'Sample'[Title] <=MAX('Sample'[Title]))
    CumulativeRun = 
CALCULATE(
    SUM('Sample'[TotalRun]),
    'Sample'[Title] <=MAX('Sample'[Title]))

    Thanks,

    Arul

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Sorry that doesn't seem to work for cumulative:

      • Arul's avatar
        Arul
        Icon for Super User rankSuper User
        3 years ago

        Anonymous ,

        Can you share the modified formula you have used?

        Thanks,

        Arul