Return a minimum number of a calculated measure of a max measure

[OPJayhawk]

The Month column is straight from the table and the TotalCount is a measure (counta(Members)) counting the number of members in that month. 

Month	TotalCount
1	596
2	552
3	608
4	484
5	292
6	209
7	187

I'd like a SuggestedMonth measure that shows at which month the TotalCount is less than 40% of the max TotalCount, rounding down.

Looking at the data, max(TotalCount) = 608 * .4 = 243 (rounded down)

So month 6 is the expected result which is the minimum month at which the total count dipped below the threshold.

All of my CALCULATE, IF and MAXX functions have returned PLACEHOLDER or TRUE/FALSE errors or that TotalCount can't be used in this expression. 

[OPJayhawk · Accepted solution]

So @VahidDM sent me down the right path by using variables, return and filter.

The main problem was trying to take the maximum of a measure. Max requires a column, so I had to build a virtual table using SUMMARIZE and take the maximum of one column from that virtual table. I was then able to filter that virtual table based on the threshold variable I had previously defined.

Looking at the data, I found that I didn't want to include the month in which patientCount fell below the threshold, so I subtracted one from the month and then made an IF to account for any Month=-1 value to return "ALL".

SuggestedMonth = 
Var TableVar=
SUMMARIZE(Table
                        , Table[Month]
                        ,"PatientCount"
                        , counta(Table[Members]))
Var ThreshVar=
MAXX(
    TableVar
    ,[PatientCount]*.4)

Return
                IF((
                    MINX(Filter (TableVar,[PatientCount] < ThreshVar) 
                    ,Table[Month])-1)<0
                    ,"ALL"
                    ,MINX(Filter (TableVar,[PatientCount] < 
                    ThreshVar),Table[Month])-1
                   )

[VahidDM]

Hi @OPJayhawk 

Try this measure:

Month Less %40 = 
VAR _A =
    ROUND ( MAX ( 'Table'[TotalCount] ) * 0.4, 0 )
VAR _B =
    CALCULATE (
        MAX ( 'Table'[TotalCount] ),
        FILTER ( ALL ( 'Table' ), 'Table'[TotalCount] < _A )
    )
RETURN
    CALCULATE (
        MAX ( 'Table'[Month] ),
        FILTER ( ALL ( 'Table' ), 'Table'[TotalCount] = _B )
    )

Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[OPJayhawk]

Thanks for your help. 

I still get an error: "Column 'TotalCount' in table 'Table' cannot be found or may not be used in this expression."

Is that related to TotalCount being a measure? I have a lot of other columns in my data but I only thought these 2 were necessary to include. Anyway, I'll keep playing with it. Thanks again!