cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

## Average by category AND date

I'd like to calculate the average of CMg for every Bloque Horario category (A, B and C). I tried with the following measure which works only if select one month. However, my data has 10 years with 12 months each one:

```AverageMeasure =
CALCULATE (
AVERAGE ( Data[CMg] ),
FILTER ( ALLSELECTED ( Data ), Data[Bloque Horario] = MAX ( Data[Bloque Horario] ) )
)```

1 ACCEPTED SOLUTION
Solution Supplier

Hi @dvhez ,

From your comment: calculate only the block (A, B or C) corresponding to each month and year?

It now sounds like you do not need  the Year|Month|Hour  to average by but Year|Month ?

Is this the result you are after?

Jan 2019[ A] = 44.03 , Jan 2019 [B] = 43.0
Feb 2019 [A] = 45.62 , Feb 2019 [B] = 44.59

If so here is the code for this one. Note, filter context is crucial when using these calculations

```_Measure5 =
CALCULATE (
AVERAGE(Data[CMg] ),
FILTER ( ALL( Data ), Data[Bloque Horario] <= MAX ( Data[Bloque Horario] )  && Data[Date] <= MAX(Data[Date])
))```

13 REPLIES 13
New Member

how can we calculate Month Average for each month for combined year. For eg how can we create Month Average for Jan as a single value where it adds all Jan values and calculate average (eg from 2017,2018,2019…..2023)

and similarly for Feb, Mar... Dec

Super User

Hi,

Share some data, explain the question and show the expected result.

Regards,
Ashish Mathur
http://www.ashishmathur.com
New Member

Super User

Hi,

Share the link from where i can download your PBI file.  Also, it will be nice to also see your calculation in an Excel workbook so that i can compare my DAX formula's result with your Excel formula's result.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper II

The output logic and result would be the same as the picture I posted minutes ago.

Solution Supplier

Hi @dvhez,

Depending on your final required result to display, you can create a measure which changes the average when a filter is slected or calcualtes the average regardless of the filters selected.

Here are the options for code, ALL SELECTED will filter your results and change the avearge based on selected filters and ALL will give you the average for you category regrdless of the filters used.

```AveragePerCategory-ChangesWithFilter =
CALCULATE (
AVERAGE ( Example1[Amount] ),    FILTER ( ALLSELECTED(  Example1 ) , Example1[Castegory] = MAX ( Example1[Castegory]) )
)```

```AveragePerCategory-DoesNotChangeWithFilter =
CALCULATE (
AVERAGE ( Example1[Amount] ),    FILTER ( ALL(  Example1 ) , Example1[Castegory] = MAX ( Example1[Castegory]) )
)```

Date selected and 2 different results shown

Helper II

@MariaP Thank you!. However, I have several months and year but I just want to calculate the average of block A for each month and each year. With this solution having a table without any filter would show the same value for block A for every month and every year. How can I make the measure to calculate only the block (A, B or C) corresponding to each month and year?

Example of hypothetical year with 2 months, 4 hours and 2 blocks (A: first two hours and B: the next two):

Solution Supplier

Hi @dvhez ,

From your comment: calculate only the block (A, B or C) corresponding to each month and year?

It now sounds like you do not need  the Year|Month|Hour  to average by but Year|Month ?

Is this the result you are after?

Jan 2019[ A] = 44.03 , Jan 2019 [B] = 43.0
Feb 2019 [A] = 45.62 , Feb 2019 [B] = 44.59

If so here is the code for this one. Note, filter context is crucial when using these calculations

```_Measure5 =
CALCULATE (
AVERAGE(Data[CMg] ),
FILTER ( ALL( Data ), Data[Bloque Horario] <= MAX ( Data[Bloque Horario] )  && Data[Date] <= MAX(Data[Date])
))```

Helper II

Thank you @MariaP , it works!.

Super User

Hi,

This picture should say it all

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper II

Thanks @Ashish_Mathur , If I understood correctly, I created a measure Value = SUM(Data[CMg]) and then another measure Measure = AVERAGEX(ALL(Data[Hora]);[Value]), is it right?

If it is, I don't get the result I want (the "Measure" column is supposed to be the same as it is averaging the "A" hourly blocks for each month and each year)

Super User

As you can see from the image that i posted, the formula works fine.  Try again.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper II

Yes @Ashish_Mathur , I'm trying. But am I doing it right? I entered two different measures in the way I exposed above but although they are exactly your formulas it doesn't work. "Measure" is equal to "Value" for every row.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors