cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## 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

2 REPLIES 2
Community Support Team

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

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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

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

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.

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!

Announcements

#### Community News & Announcements

Get your latest community news and announcements.

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 330 members 3,357 guests
Recent signins: