Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Average for first two non blank values

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

 

NameJanFebMarAprilMayAverage of first two Months
Bob807010102060
James  40602050
Suzy 7090505080

 

Any help would be much appreciated 🙂


Need Help
  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    6 years ago

    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.

4 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    6 years ago

    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.

  • AllisonKennedy's avatar
    AllisonKennedy
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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. 

  • az38's avatar
    az38
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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
  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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.