Minimum of Monthly Averages

[Anonymous]

I'm looking to create a measure that will output the minimum of an average of values by month. I've got many values for each month. I want to calculate an average for each month and then find the minimum of that.  I don't want to have to manually specify months or update it annually. Essentially I need a function like this

AVERAGEBY([VALUES],[CONDITION])
AVERAGEBY([VALUES],[YEAR-MONTH])

[v-jiascu-msft]

Hi @Anonymous ,

Could you please mark the proper answers as solutions?

Best Regards,

[OwenAuger]

Hi @Anonymous 

The basic structure of the measure you want to create is:

=
MINX (
    VALUES ( Date[Year-Month] ),
    CALCULATE ( AVERAGE ( YourTable[Value] ) )
)

Replace the column names with the relevant column names and that should work.

This measure iterates over the values of Year-Month (in the current filter context), performs the calculation in red in the context of each Year-Month in turn, then takes the minimum value.

Regards

Owen

[Anonymous]

Hi and thanks for your reply. I don't think I explained myself well enough. The method your provided gives me a total average, not the minimum of monthly averages. Here's the output where TEST uses the method you described:



I need the result to be the value from 2019-2 of 6.944, regardless of what month it shows in. 

[OwenAuger]

Hi again,

No problem, that clarifies things a bit 🙂

To give the exact syntax I would need to see the tables and relationships, but here's a generic modified version of my original measure based on what you just posted.

Replace TableWithDates & TableWithDuration with appropriate names (they might be the same table).

=
MINX (
    CALCULATETABLE (
        SUMMARIZE ( TableWithDates, TableWithDates[Year], TableWithDates[Month] ),
        ALLSELECTED ()
    ),
    CALCULATE ( AVERAGE ( TableWithDuration[DURATION] ) )
)

[Anonymous · Accepted solution]

Hi again, I'm still not getting the result I need using those methods. It still produces a different value for each month.  

I've found something of a work around. It's not ideal but it works. I pull in a power pivot table that shows the average monthyly value, clone that into a table with cell references and add that table to the data model. Then I can do a simple measure like this: 

Type A BIBU:=
CALCULATE(
MIN('Standards'[Type A]),
FILTER(
'Standards',
'Standards'[Type A]>0
)
)

My apologies, I'm don't know how to properly format the code here. 

Edit: Due to time constraints I was unable to follow up with the other possible solutions proposed by OwenAuger.

[OwenAuger]

Sorry that's not working.

My last measure worked on a dummy model at my end, but clearly something is different in your data model that is preventing it working.

If you could post a cloud storage link to a santised file that would help solve this. Otherwise post the relevant tables here with an image of the data model tables/relationships.

Regards,

Owen

[Anonymous]

Thanks for following up again. 

Here is a link to some dummy data: https://drive.google.com/open?id=1G3s3glR9sj88wv0uqWCQR96N1rbO49Ok

Ideally I'd produce two types of measures. Refer to the pivot table I've generated. One would take the minimum by fleet, ignoring the different sites. The other type of measure would take the minimum by fleet and site. I hope this helps.

[OwenAuger]

Thanks @Anonymous - I've been a little delayed in getting back to this.

Just to make sure, would you mind putting a few sample values of the two measures you're talking about (minimum by fleet ignoring site & minimum by fleet & site) alongside the pivottable in your uploaded file?

I'm sure we can produce the output you want, for example using one of the ALL functions to ignore filters on Fleet or Site. Just want to be 100% sure how you're defining the measures.

Regards,

Owen