Forum Discussion

mrenzo01's avatar
mrenzo01
Frequent Visitor
8 years ago

Running Total w/ Less Than or Equal Condition (not dates)

Hello,

 

I am trying to calculate the running total within a data set of people. Each person has an attribute "Highest Level" that indicates the highest level they achieved across an 11 step process. I am trying to display the running total count of people to reach each level where anyone that reached level 11 should also be counted in all levels that preceded it (10, 9 8, etc). I am trying to accomplish this by using a combination of CALCULATE, COUNTROWS, FILTER, and MAX (as I have seen in other forums) but the result is not accurate. In the provided graphic, the correct result for Level 3 should be 701 (453 +248), What am I doing wrong?

 

The formula I am using is as follows:

 

Reach Level = CALCULATE(
    COUNTROWS(TableName),
    FILTER(
        ALLSELECTED(TableName),
        TableName[Highest Level]<=max([Highest Level])
        )
    )

 

Reach Level = CALCULATE(
COUNTROWS(TableName),
FILTER(
ALLSELECTED(TableName),
TableName[Highest Level]<=max([Highest Level])
)
)

 

I tried using the example found here but needed an ALLSELECTED function to account for other report filters applied: https://community.powerbi.com/t5/Desktop/Running-Count/td-p/216728

Need Help

2 Replies

  • v-piga-msft's avatar
    v-piga-msft
    Icon for Resident Rockstar rankResident Rockstar
    8 years ago

    Hi mrenzo01,

     

    Do you want to get the output like below?

     

     

    If it is, you could try the formula below.

     

    Reach Level =
CALCULATE (
    SUM ( 'Table1'[End at level] ),
    FILTER (
        ALLSELECTED ( Table1 ),
        'Table1'[Highest Level] <= MAX ( [Highest Level] )
    )
)

    If not, please share some data sample and your desired output so that we could help further on it?

     

    Best Regards,

    Cherry

    • mrenzo01's avatar
      mrenzo01
      Frequent Visitor
      8 years ago

      That is the right output but, unfortunately, that function will not work because the pivot column "End at Level" is also a measure. The error returned when I try to implement the proposed solution is: "The SUM function only accepts a column reference as the argument number 1".

       

      I took your advice and created a dummy data set that recreates my issue, provided in Excel and shared from dropbox (link below). The "Source Data Sample" sheet is a data table with two columns. The first is a unique identifier for all people (the item to be counted) and the second is that unique values to be counted against (levels 2 through 11). This dataset is provided using the same outcomes found in my larger data set so it should return the same output. I also provided a sample of the solution in Excel, which matches your values.

       

      Dropbox Link: https://www.dropbox.com/s/ele5n144s4k3kel/Running%20Count%20sample%20data.xlsx?dl=0

       

      Forgive me but I am new to the forums and unsure of what the proper etiquette or method to share data is, hence the shared dropbox file. 

       

      Thank you v-piga-msft for your first attempt and any follow-up!