Average by category AND date

[dvhez]

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] ) )
)

[Deepraj]

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

[Ashish_Mathur]

Hi,

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

[Deepraj]

 

[Ashish_Mathur]

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.

[dvhez]

Hi @Ashish_Mathur , you can download the pbix from here:

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

[MariaP]

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

[dvhez]

@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):

[MariaP · Accepted solution]

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])
))

[dvhez]

Thank you @MariaP , it works!. 

[Ashish_Mathur]

Hi,

This picture should say it all

[dvhez]

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)

[Ashish_Mathur]

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

[dvhez]

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.