Forum Discussion

dvhez's avatar
dvhez
Helper II
7 years ago
Solved

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

 

 

 

 

 

Need Help
  • MariaP's avatar
    MariaP
    7 years ago

    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

  • MariaP's avatar
    MariaP
    Solution Supplier
    7 years ago

    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's avatar
      dvhez
      Helper II
      7 years ago

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

       

       

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 years ago

    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's avatar
      dvhez
      Helper II
      7 years ago

      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.

       

  • Deepraj's avatar
    Deepraj
    New Member
    2 years ago

    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