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

[mrenzo01]

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

[v-piga-msft]

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]

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!