Average for first two non blank values

[Anonymous]

Hi Everyone,

I am trying to calculate the average of the first two non blank months for each person.

The products sold are calculated using a measure.

Products Sold

Name	Jan	Feb	Mar	April	May	Average of first two Months
Bob	80	70	10	10	20	60
James			40	60	20	50
Suzy		70	90	50	50	80

Any help would be much appreciated 🙂

[v-zhenbw-msft · Accepted solution]

Hi @Anonymous ,

We can use the following measure to meet your requirement.

Measure =
VAR min_date =
    CALCULATE (
        MIN ( 'Table'[Month Value] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Name] = MAX ( 'Table'[Name] )
                && 'Table'[Value] <> BLANK ()
        )
    )
VAR _firstMonth =
    CALCULATE (
        MAX ( 'Table'[Month] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Month Value] = min_date
                && 'Table'[Name] = MAX ( 'Table'[Name] )
        )
    )
VAR _secondMonth =
    CALCULATE (
        MAX ( 'Table'[Month] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Month Value] = min_date + 1
                && 'Table'[Name] = MAX ( 'Table'[Name] )
        )
    )
VAR first_value =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        ALLEXCEPT ( 'Table', 'Table'[Name] ),
        'Table'[Month] = _firstMonth
    )
VAR second_value =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        ALLEXCEPT ( 'Table', 'Table'[Name] ),
        'Table'[Month] = _secondMonth
    )
RETURN
DIVIDE ( first_value + second_value, 2 )

The result like this,

If you want the result like your desire result table, please refer the following steps.

1. Create a table that contains the matrix column’s name.

2. Then we can create a measure to calculate the value of each month.

values = CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Month]=MAX('Table (2)'[Column])))

3. At last we can create a measure to get the desire result.

Noblank_two_Month = 
SUMX (
    VALUES ( 'Table (2)'[Column] ),
    SWITCH (
        'Table (2)'[Column],
        "Jan", [values],
        "Feb", [values],
        "Mar",[values],
        "Apr",[values],
        "May",[values],
        "Noblank_two_Month",[Measure]
        )
)

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

BTW, pbix as attached.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[az38]

Hi @Anonymous 

try create a measure

Measure = 
var _firstMonth = CALCULATE(MIN('Table'[Month]), ALLEXCEPT('Table','Table'[Name]), 'Table'[Value] <> BLANK())
var _secondMonth = CALCULATE(MIN('Table'[Month]), ALLEXCEPT('Table','Table'[Name]), 'Table'[Value] <> BLANK(), 'Table'[Month] > _firstMonth)
RETURN 
(
CALCULATE(MAX('Table'[Value]), ALLEXCEPT('Table','Table'[Name]), 'Table'[Month] = _firstMonth) + 
CALCULATE(MAX('Table'[Value]), ALLEXCEPT('Table','Table'[Name]), 'Table'[Month] = _secondMonth)
)/2

[AllisonKennedy]

@Anonymous  What does the measure calculation look like and how does the raw data table look? I agree that variables can help here, but need more info from you to provide more detailed, helpful response. 

[lbendlin]

Use variables.

you find the first variable with FIRSTNONBLANK. 

In a second variable note the month for that value

Then you can find the second value, again with FIRSTNONBLANK, but now with a filter restriction that the month needs to be bigger than the second variable.