Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
pocorniy
Regular Visitor

how to make averageifs as a measure

Hi there,

i am trying to make a new measure in my data, this measure is supposed to be the same as AVERAGEIFS in excel, it looks like 

sc by group = 
CALCULATE(
    AVERAGE('AGGREGATE_GLOBAL'[sc]),
    FILTER (
        'AGGREGATE_GLOBAL',
        [year] = EARLIER ( 'AGGREGATE_GLOBAL'[year] )
        && [month] = EARLIER('AGGREGATE_GLOBAL'[month])
        && [group_store] = EARLIER('AGGREGATE_GLOBAL'[group_store])
    )
)

and should count an average value of column [sc] by [year], [month] and [group_store]. 

Unfortunately, as far as I know I cannot use the function EARLIER in measure(it returns

ARLIER/EARLIEST refers to an earlier row context which doesn't exist

), so then I've tried to make a new calculated column with the same code. But in that case it returns 
Function 'CALCULATE' is not allowed as part of calculated column DAX expressions on DirectQuery models.

Because I use DirectQuery mode and cannot use Import.

 

How can I solve it?

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @pocorniy,

According to your description, it sounds like you want to apply multiple aggregation on your records calculations.

For this scenario, you can take a look the following link about multiple aggregation calculation in measure expressions if helps:

Measure Totals, The Final Word  

Regards,

Xiaoxin Sheng

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

Hi @pocorniy,

According to your description, it sounds like you want to apply multiple aggregation on your records calculations.

For this scenario, you can take a look the following link about multiple aggregation calculation in measure expressions if helps:

Measure Totals, The Final Word  

Regards,

Xiaoxin Sheng

Walter_W2022
Resolver II
Resolver II

@pocorniy I think you need to clarify the granularity, do you want to the average for daily, monthly, quarterly or yearly? then you can use variable to create a virtual table to calculate the average, hopefully this will help.

@Walter_W2022 in short, to clarify what I need, I have a table with this columns (not all, but enough):

year; month; store; group_store; sc

2022; 8; 23; A; 12

2023; 9; 15; B; 18

etc.

So, i need a measure that will calculate an average value of the column sc for each year, month and group_store. I need it in order to see 2 lines in a graph: first one is value for one store (or average for couple of them, depends on filters) and second one that will reflect an average value for this particular group of stores (i have three -- A, B, C). When I choose one store in filter, i want to see an average value for all stores in his group. 

How can I do this?

thanks for help

I have montly granularity of data and want to calculate the average value also monthly;

I need this value to be in my general table for the correct vizualization. Is it possible?

 

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.