SUM row if SUM <x

[tborg]

I have a data table that looks like this:

Total RPs	Total 360s	Total RTAs
0	0	0
7	0	6
11	2	3
2	5	13
0	1	1
0	0	1
0	0	0
8	0	0
0	0	0
0	4	3

I want to sum across the row, then count the number of sums that are less than a target number, let's say 5. 

So in this example, the calculation would result in this:

Total FPs
0
13
16
20
2
1
0
8
0
7

and the number 5 would appear in my table (as there are 5 rows that have a result less than 5).

So the measure I started with was "If the sum across the rows is >=1 and <=4, then count the number of rows that gave that result."

This is my formula so far:

Total FPs =

(IF(SUMX(Advisors,Advisors[RTAs] + [Pathfinders] + [Financial 360s])>=1

&&

SUMX(Advisors,Advisors[RTAs] + [Pathfinders] + [Financial 360s])<=4,
DISTINCTCOUNT(Advisors[Advisor Number])). 

Since it is giving me a blank, it obviously is not doing what I want it to!

[mattbrice]

How are you trying to visualize this?  as a single number?  Or in a Matrix?  What are on rows/columns? anything?

Here is a shot in the dark guessing at results wanted:

measure =
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            VALUES ( ADVISORS[Id] ),
            "row_total", SUMX ( Advisors, Advisors[RTAs] + [Pathfinders] + [Financial 360s] )
        ),
        [row_total] < 5
    )
)

[tborg]

Yes, as a single number.  I tried to paste a screenshot but won't let me!

I will try your possible solution and report back.

Thanks!

[tborg]

Still gave me a blank.  I will begin again tomorrow.

[mattbrice · Accepted solution]

Sorry, looking closer it needs to have a CALCULATE in there to work right:

measure:=COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            VALUES ( Advisors[Id] ),
            "row_total",  CALCULATE( SUM( Advisors[Total RPs] ) + SUM( Advisors[Total RTAs]) + SUM( Advisors[Total 360s] ) )
        ),
        [row_total] < 5
    )
)

 This also assumes you have an 'Id' column in the 'Advisors' table to uniquely identify each row.

[tborg]

That worked, thanks!  How would I change it if I wanted only those with 1-4 sales (i.e. don't count those with zero)?