Groupby and return values based on another column

[klintala]

I want to return the Value at the max Date for each ID. (then avergage those values)

I am able to return the max Value for each ID, but not the value at the max Date of each ID.

Here is what I have so far:

Average_Max_Day = 
AVERAGEX (
    GROUPBY (
        test_table,
        test_table[ID],
        "max_value", MAXX ( CURRENTGROUP (), test_table[Value] )
    ),
    [max_value]
)

For those familiar with python, I can achieve the desired results with this code:

import pandas as pd
df = pd.read_excel("test.xlsx",usecols=['ID','Date','Value'])
df_grouped = df.loc[df.groupby(['ID'])['Date'].idxmax()]
df_average = df['Value'].mean()

[mimimimimi]

Hi @klintala 
I am trying to accomplish this "group by" below from my Python code. I am trying to get the median of each of them.

the group by on power Bi is not working, can you please advice?

Median_Day =

AVERAGEX (

    GROUPBY (

        'Cycle Time MOI',

        'Cycle Time MOI'[CALENDAR_YEAR_MONTH],

        ,'Cycle Time MOI'[COVERAGE_GROUP_CODE]

        ,'Cycle Time MOI'[FIRST_COMPLETED_MOI]

        ,'Cycle Time MOI'[LINE_OF_BUSINESS]

        ,'Cycle Time MOI'[TOTAL_LOSS]

        ,'Cycle Time MOI'[PRODUCT_GROUP_NAME]

        "median_value", median( CURRENTGROUP(),'Cycle Time MOI'[RPT_REPORTED_COUNT]

         ,'Cycle Time MOI'[RPT_LOSS_TO_RPT_DAYS]

        ,'Cycle Time MOI'[CNTCT_CNT]

        ,'Cycle Time MOI'[CNTCT_RPT_TO_CNTCT_DAYS]

        ,'Cycle Time MOI'[EST_CNT]

        ,'Cycle Time MOI'[EST_CNTCT_TO_EST_DAYS]

        ,'Cycle Time MOI'[PAY_EST_TO_PAY_DAYS]

        ,'Cycle Time MOI'[PAY_EST_CNT]

        ,'Cycle Time MOI'[PAY_RPT_CNT]

        ,'Cycle Time MOI'[PAY_RPT_TO_PAY_DAYS])

    ),

    [median_value]

)

Many thanks for considering my request.

[AllisonKennedy · Accepted solution]

@klintala 

You can use a variable to store the Max date, then find the value for that date: 

Average_Max_Day = 
AVERAGEX( Values( test_table[ID]) ,
   VAR _ID = test_table[ID]
   VAR _MaxDay = MAXX(FILTER(test_table, test_table[ID] = _ID), test_table[Date] )
   RETURN
   SUM( test_table[Value] ) --change this to average or max depending on how you want to aggregate the value per day per ID
    )
    

[klintala]

That did the trick. Thank you very much. How have I never realized we can write variables inside functions?

I'm still not entirely sure how this works, but I was able to play around with it to see how I can get different results.

[AllisonKennedy]

@klintala  Awesome - so glad it worked. Variables inside functions is an advanced DAX concept and a great way to manipulate the evaluation context of the variable. You've just opened up a whole new world. 🙂